Loading a CSV From a URL Into PostgreSQL Without Saving It

Plenty of CSV data is published at a URL: a government dataset, a vendor's nightly export, a file in object storage behind a signed link. You can download it and load the file, or you can stream the response into COPY and never touch the disk. The streaming version is two commands and a few ways to get it wrong, all of which come down to a pipeline reporting success when the download failed. This article builds the load, breaks it three times on purpose, and ends with the guards I use. The usual route for a file you already have is in the PostgreSQL loading guide.

The load

curl -s hides the progress meter, and -f makes it fail on an HTTP error status instead of printing the error page as if it were the body. With those two flags, the pipe is:

psql -q -c "TRUNCATE bench"
curl -sf http://127.0.0.1:8099/ok.csv | psql -c "COPY bench FROM STDIN WITH (FORMAT csv, HEADER true)"

COPY 2000000

COPY 2000000, the same count as loading the local file. The data went from the server to curl to psql to PostgreSQL without being written anywhere on the client. If the URL needs authentication, pass a header with -H "Authorization: Bearer $TOKEN" and keep the token in an environment variable, since the command line of a process is visible to other users on the machine. A redirect needs -L. A server that compresses with gzip needs --compressed, which makes curl ask for it and decode it. The curl manual lists each flag, and --fail and --fail-with-body are the pair to know.

The same load from inside psql, so that the shell is not involved:

psql -q -c "TRUNCATE bench"
psql -c "\copy bench FROM PROGRAM 'curl -sf http://127.0.0.1:8099/ok.csv' WITH (FORMAT csv, HEADER true)"

COPY 2000000

Both are equivalent on a clean download. The difference is what happens when it is not.

Failure 1: the URL does not exist

A pipeline's exit status is the status of its last command. Ask for a file that is not there, and curl -f fails with exit code 22 and writes nothing to the pipe. psql receives an empty input, which is a valid COPY of zero rows, and exits 0:

psql -q -c "TRUNCATE bench"
curl -sf http://127.0.0.1:8099/missing.csv | psql -c "COPY bench FROM STDIN WITH (FORMAT csv, HEADER true)"
echo "pipeline exit status: $?"

COPY 0
pipeline exit status: 0

COPY 0 and pipeline exit status: 0. A script that checks the exit status believes it loaded the file. If the table is truncated first, as in a nightly reload, the table is now empty and the job is green. This is the most expensive version of the bug, because the failure is silent and the consequence is an empty table.

set -o pipefail, described in the bash manual, changes the pipeline's status to the last nonzero status of any stage:

psql -q -c "TRUNCATE bench"
bash -c 'set -o pipefail; curl -sf http://127.0.0.1:8099/missing.csv | psql -c "COPY bench FROM STDIN WITH (FORMAT csv, HEADER true)"; echo "pipeline exit status: $?"' 2>&1

COPY 0
pipeline exit status: 22

The exit status is 22, curl's code for a failed HTTP request under -f. The COPY 0 was still printed and the load still ran, but the script now knows. Put set -euo pipefail at the top of every script that pipes into a database, and wrap the load in a transaction that you can roll back on failure, so a failed download does not leave a truncated table.

Failure 2: the download is cut off

A server that dies, a proxy that times out or a network that drops can end a response early. The server in my test promises the whole file with a Content-Length header and then stops sending halfway, which is what a broken connection looks like to curl:

psql -q -c "TRUNCATE bench"
curl -sf http://127.0.0.1:8099/cut.csv | psql -c "COPY bench FROM STDIN WITH (FORMAT csv, HEADER true)"
echo "pipeline exit status: $?"
psql -qAt -c "SELECT 'rows now in the table: ' || count(*) FROM bench"

ERROR:  invalid input syntax for type timestamp: "2025-08-"
CONTEXT:  COPY bench, line 1008363, column created_at: "2025-08-"
pipeline exit status: 1
rows now in the table: 0

The load failed with invalid input syntax for type timestamp: "2025-08-" at line 1008363, because the cut fell in the middle of a created_at value, and the table is empty. That is a good outcome and a lucky one. The pipeline exit status came from psql's failure, not from curl. If the cut had landed on the last character of a row, or inside a free text field, psql would have received a valid file with fewer rows and returned 0, and curl's failure, exit code 18 for "transfer closed with outstanding data", would have been lost without pipefail.

\copy FROM PROGRAM sees the same failure and reports it. The output has the data error and then the child's status:

psql -q -c "TRUNCATE bench"
psql -c "\copy bench FROM PROGRAM 'curl -sf http://127.0.0.1:8099/cut.csv' WITH (FORMAT csv, HEADER true)" 2>&1
psql -qAt -c "SELECT 'rows now in the table: ' || count(*) FROM bench"

ERROR:  invalid input syntax for type timestamp: "2025-08-"
CONTEXT:  COPY bench, line 1008363, column created_at: "2025-08-"
curl -sf http://127.0.0.1:8099/cut.csv: child process exited with exit code 18
rows now in the table: 0

The final line says child process exited with exit code 18 and zero rows are in the table. COPY treats a nonzero exit from the program as a failure, so even a cut at a row boundary would have rolled the load back. That is why I use the PROGRAM form in scripts.

The checks that do not depend on luck

The COPY reference says nothing about where its input comes from, and that is the point: the server sees a stream and has no idea whether it ended where the sender meant it to.

Because a CSV has no end marker, a truncated file that ends on a row boundary is indistinguishable from a complete one by looking at the file. So the guards have to come from outside it.

The first is the transport's own guard, which pipefail and \copy FROM PROGRAM surface: curl knows the Content-Length and fails when it is not met.

The second is a count. If the provider publishes how many rows the file should have, in a header, a sidecar file or a documented API field, compare it with the COPY n tag. If it does not, compare with yesterday's count and alarm on a drop of more than a few percent.

The third is a checksum. A provider that publishes a SHA-256 lets you verify the whole body. Download to a temporary file, compare, then load. For my test server, the downloaded copy and the original had the same digest:

curl -sf http://127.0.0.1:8099/ok.csv -o /tmp/dl.csv && sha256sum /tmp/dl.csv orders-2m.csv | awk '{print substr($1,1,16), $2}'

a1e5bb681d8316a2 /tmp/dl.csv
a1e5bb681d8316a2 orders-2m.csv

Both files hash to the same prefix a1e5bb681d8316a2. That costs a copy on disk and is worth it for any file that matters. For a large file, check the checksum while streaming with tee, writing the stream to a file as well, and load from the stream. You then have both the load and the evidence.

Security and cost

Loading from a URL means trusting the URL. Use HTTPS, leave certificate checking on, and do not pass -k. Pin the host in a variable rather than building it from user input, since a pipeline fed by a user supplied URL is a way to read internal addresses. Do not echo a signed URL into logs: it is a credential. And watch the bill: pulling a large file out of a cloud provider's storage across regions or out to the internet costs per gigabyte, and a retry loop around a 20 GB download can cost real money.

How to tell it worked

Three lines of output tell you: COPY n with the number you expect, a pipeline or program status of 0, and, for a file you can verify, a matching checksum. In a script I print all three. After a nightly load I also compare the table's row count with the previous night's, which catches the case where everything succeeded and the provider sent a short file.

When streaming from a URL is the wrong plan

If the file is small and you want to look at it, download it first. It is easier to inspect, you can run the validation checks on it and a failed load can be re-run without fetching again. If the endpoint is flaky or rate limited, a download with retries to a file, then a load from the file, separates the two failure modes, and the load can be retried alone. If the data comes in many small files, a loop of downloads and loads is more manageable than one clever pipe. And if the source is a cloud bucket, the provider's own bulk import, such as an aws_s3 extension on RDS or the equivalent, reads the object directly, so nothing passes through your machine.

The related case, a file that arrives compressed, is covered in loading a gzipped CSV, and the same pipefail and program checks apply.