0

No rows are being copied when I try to copy a CSV from stdin. To be more specific, this works when writing the command on the cli, but not when read as part of a file. Why is reading SQL commands from a file different from executing them directly as an argument for -c?

cat extract.csv  | psql postgres -f copy-user.sql
Timing is on.
Line style is unicode.
Border style is 2.
Null display is "[NULL]".
Expanded display is used automatically.
COPY 0
Time: 41.685 ms

$ cat extract.csv  | psql postgres -c "$(cat copy-user.sql)"
Timing is on.
Line style is unicode.
Border style is 2.
Null display is "[NULL]".
Expanded display is used automatically.
COPY 10
Time: 31.685 ms

copy-user.sql

copy to_update from stdin (delimiter ';', format csv);

extract.csv

bfb92e29-1d2c-45c4-b9ab-357a3ac7ad13;test@test90239023783457843.com;x
aeccc3ea-cc1f-43ef-99ff-e389d5d63b22;tester@testerkjnaefgjnwerg.no;x
9cec13ae-c880-4371-9b1c-dd201f5cf233;bloblo@gmail.com;x
aeada2bc-a362-4f3e-80f2-06a717206802;vet@gmail.com;x
fb85ddd8-7d17-4d41-8bc3-213b1e469506;navnnavnesen@ptflow.com;x
528e1f2e-1baa-483b-bc8c-85f993014696;kklk@hotmail.com;x
dbc8a9c1-56cf-4589-8b2c-cf1a2e0832ed;ghiiii@hotmail.com;x
fbf23553-baa2-410a-8f96-32b5c4deb0c7;lala@lala.no;x
e22ec0de-06f9-428a-aa3e-171c38f9a1f7;x2@gmail.com;x
8e8d0f73-8eb7-43b4-8019-b79042731b97;mail@mail.com;x

table definition for to_update

create table to_update(id text, email text, text char);
0

The answer was to be found in this pgsql-sql mailing list archive:

psql's interpretation of "stdin" when reading from a file is that the COPY data is to come from that same file (look at the script produced by pg_dump for an example). So it reads to the end of the file, which is right away --- otherwise you'd get some incorrect-data errors. The data sourced from the cat command is never noticed at all.

The thread also lists 4 solutions for achieving the same thing:

1) using COPY FROM STDIN

cat event.csv | psql -c "$(cat event.sql)"

2) using COPY FROM STDIN

psql -f  <(cat event.sql event.csv)

3) using \COPY FROM PSTDIN

cat event.csv | psql -f event.sql

4) using \COPY FROM STDIN

psql -f  <(cat event.sql event.csv <(echo "\.")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.