BUG #15645: \COPY command not printing output in batch mode
The following bug has been logged on the website:
Bug reference: 15645
Logged by: placebo
Email address: sandeshyapuram@gmail.com
PostgreSQL version: 11.2
Operating system: Linux/Mac
Description:
I am trying to truncate a table and copy data into the same table using
\copy. In this process, I need to print number of rows deleted and copied.
I am using this command:
echo -e "begin; delete from test; \\\copy test from test.csv CSV HEADER;
end;" | psql -hlocalhost -dpostgres -Upostgres
Expected output:
---------------------
BEGIN
DELETE 4
COPY 2
COMMIT
---------------------
Actual output:
---------------------
BEGIN
DELETE 4
COMMIT
---------------------
As you can see, the output clearly is missing the copy count.
Interestingly, this works if I try the following command:
echo -e "begin;\ndelete from test;\n\\\copy test from test.csv CSV
HEADER;\nend;" | psql -hlocalhost -dpostgres -Upostgres
Why do I need to add a new line character when I am already using the
separator?
PG Bug reporting form wrote:
I am trying to truncate a table and copy data into the same table using
\copy. In this process, I need to print number of rows deleted and copied.I am using this command:
echo -e "begin; delete from test; \\\copy test from test.csv CSV HEADER;
end;" | psql -hlocalhost -dpostgres -Upostgres
As answered at [1]https://dba.stackexchange.com/questions/230230/ where it was asked as a question,
it's not a bug, but the effect of \copy processing the whole line,
as documented:
"Unlike most other meta-commands, the entire remainder of the line
is always taken to be the arguments of \copy"
plus the fact that from the COPY + END commands executed in the
same compound statement, only the last result is handled/displayed
by psql, which is also known and documented.
Suggested workarounds:
1. use psql -1 instead of adding your own BEGIN/END commands.
2. use the heredoc syntax for multi-line SQL scripts embedded into
shell commands.
[1]: https://dba.stackexchange.com/questions/230230/
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite