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

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Interpretation of docs for \copy ... from stdin inaccurate when using -c
Date: 2022-07-23 01:28:41
Message-ID: CAKFQuwbpeORDQ5JmWg8S-WnNiJrk_VsjAvqHf5v_JcA+ataViA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

This works:

vagrant(at)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(at)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(at)vagrant:/usr/local/pgsql/bin$ echo 'value1' | ./psql -d postgres -c
'copy csvimport from stdin;'
COPY 1

but not:

vagrant(at)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.

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2022-07-23 02:58:30 Re: pg_tablespace_location() failure with allow_in_place_tablespaces
Previous Message Tom Lane 2022-07-22 23:50:02 Re: make -C libpq check fails obscurely if tap tests are disabled