Using psql with COPY FROM STDIN works fine when executed via -c (inline command) but the same thing fails if -f (script file) is used. I've created a Docker-based test to demonstrate below; tested on MacOS w/ zsh and Debian w/ bash.
I was unable to find any relevant documentation on why this would be but I imagine it has to do with psql's special \copy functionality. Can someone help illuminate me?
# create test data
echo "1,apple
2,orange
3,banana">testdata.csv
# create test script
echo "drop table if exists fruits;
create table fruits (id INTEGER, name VARCHAR);
copy fruits from stdin with delimiter as ',' csv;
select * from fruits">testscript.pg
# create network
docker network create pgtest
# run Postgres server
echo "starting postgres server"
PG_CONTAINER_ID=$(docker run -d --name=pgtest --rm --network=pgtest -h database -e POSTGRES_USER=user1 -e POSTGRES_PASSWORD=pass1 -e POSTGRES_DB=db1 -p 6432:5432 postgres:12)
echo "sleeping for 5 seconds (wait for server to start)"
sleep 5
docker logs $PG_CONTAINER_ID
echo "*"
echo "*"
echo "*"
echo "run psql script using inline with -c"
cat testdata.csv | docker run -i --rm --network=pgtest postgres:12 psql postgres://user1:pass1@database:5432/db1 -c "$(cat testscript.pg)"
echo "*"
echo "*"
echo "*"
echo "run psql script using file with -f"
cat testdata.csv | docker run -i -v $PWD:/host --rm --network=pgtest postgres:12 psql postgres://user1:pass1@database:5432/db1 -f /host/testscript.pg
# stop server
echo "*"
echo "*"
echo "*"
docker stop $PG_CONTAINER_ID
docker rm $PG_CONTAINER_ID
The output of the psql commands look like this:
*
*
*
run psql script using inline with -c
NOTICE: table "fruits" does not exist, skipping
id | name
----+--------
1 | apple
2 | orange
3 | banana
(3 rows)
*
*
*
run psql script using file with -f
DROP TABLE
CREATE TABLE
psql:/host/testscript.pg:5: ERROR: invalid input syntax for type integer: "select * from fruits"
CONTEXT: COPY fruits, line 1, column id: "select * from fruits"
In the first case, (execution with -c), the copy data are read from standard input.
In the second case (execution with -f), the input file acts as input to psql (if you want, standard input is redirected from that file). So PostgreSQL interprets the rest of the file as COPY data and complains about the content. You'd have to mix the COPY data in with the file:
/* script with copy data */
COPY mytable FROM STDIN (FORMAT 'csv');
1,item 1,2021-11-01
2,"item 2, better",2021-11-11
\.
/* next statement */
ALTER TABLE mytable ADD newcol text;