Why does psql treat statements after COPY as stdin?

user_1056174 0
Viewed 26

Example script:

CREATE TEMPORARY TABLE foo(col1 text);
COPY foo from STDIN DELIMITER E'\t' CSV HEADER;

SELECT * FROM foo;

Execution:

psql --host=localhost --dbname=postgres --username=postgres \
  --file=my-script.sql < my-data.tsv

When I run this, something peculiar happens. SELECT * FROM foo doesn't run, and instead everything below the COPY statement is treated as rows to be entered into my table.

How can I write a SQL script that imports a data table from standard input and does further processing with it?

psql postgresql sql
1 Answers

psql treats --file scripts as if they were standard input, so it's expecting your data set to immediately follow the COPY statement. There is a workaround, as per this email on the Postgres mailing list:

cat my-data.tsv | psql -c "$(cat my-script.sql)"
user_10561740
Related