COPY command only works as command line argument, not as part of script 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 &quot;[NULL]&quot;.
Expanded display is used automatically.
COPY 0
Time: 41.685 ms

$ cat extract.csv  | psql postgres -c &quot;$(cat copy-user.sql)&quot;
Timing is on.
Line style is unicode.
Border style is 2.
Null display is &quot;[NULL]&quot;.
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;veaat@gmail.com;x
fb85ddd8-7d17-4d41-8bc3-213b1e469506;navnnavnesen@foo.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;x2aa@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);
The answer was to be found in this pgsql-sql mailing list archive:

psql's interpretation of &quot;stdin&quot; 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 &quot;$(cat event.sql)&quot;

2) using COPY FROM STDIN
psql -f  &lt;(cat event.sql event.csv)

3) using \COPY FROM PSTDIN
cat event.csv | psql -f event.sql

4) using \COPY FROM STDIN
psql -f  &lt;(cat event.sql event.csv &lt;(echo &quot;\.&quot;))