Thread: Interpretation of docs for \copy ... from stdin inaccurate when using -c

Interpretation of docs for \copy ... from stdin inaccurate when using -c

From
"David G. Johnston"
Date:
This works:

vagrant@vagrant:/usr/local/pgsql/bin$ echo 'value1' | ./psql -d postgres -c '\copy csvimport from stdin;'
COPY 1

However:

For \copy ... from stdin, data rows are read from the same source that issued the command

and

When either -c or -f is specified, psql does not read commands from standard input;

So the meta-command is not read from standard input, thus standard input is not the source of the command, yet the copy data sitting on standard input is indeed read and used for the copy.

The behavior when the \copy command is in --file does conform to the descriptions.  Thus one must write pstdin as the source to make the following work:

vagrant@vagrant:/usr/local/pgsql/bin$ echo 'value1' | ./psql -d postgres -f <(echo '\copy csvimport from pstdin;')
COPY 1

This also shows up with SQL COPY ... FROM since one is able to write:

vagrant@vagrant:/usr/local/pgsql/bin$ echo 'value1' | ./psql -d postgres -c 'copy csvimport from stdin;'
COPY 1

but not:

vagrant@vagrant:/usr/local/pgsql/bin$ echo 'value1' | ./psql -d postgres -f <(echo 'copy csvimport from stdin;')
COPY 0

This last form is especially useful for COPY ... TO STDOUT but considerably less so for COPY ... FROM; though you lose the flexibility to target pstdout (which is likewise minor).  It is an inconsistency but an understandable one.

Should we amend \copy to read:

"For \copy ... from stdin, data rows are read from the same source that issued the command (for --command the related source is stdin, see Notes), continuing..."

and then in Notes:

"The accessibility of the psql command's standard input varies slightly depending on whether --command or --file is specified as the source of commands.  For --command, the input is accessible both via pstdin and stdin, but when using --file it can only be accessed via pstdin.  This most often arises when using the \copy meta-command or SQL COPY, the latter being unable to access pstdin."

?

David J.

p.s. For now I've taken the position that figuring out what works when both --command and --file are specified is an exercise for the reader.