PostgreSQL – \COPY Not Returning Count in psql

csvimportpostgresql

I am trying to copy CSV data into tables. Also I want to print the count.
The command I'm using is: 

echo "begin; delete from a; \copy a from a.csv CSV HEADER; end;" | psql -hlocalhost -dpostgres -Upostgres

Expected Output:

BEGIN
DELETE 2
COPY 2
COMMIT

Actual Output:

BEGIN
DELETE 2
COMMIT

Any ideas on what I'm missing here?

Best Answer

Why the COPY 2 is missing

As said in the doc about \copy:

Unlike most other meta-commands, the entire remainder of the line is always taken to be the arguments of \copy

Clearly your intention is that COPY and END commands are sent consecutively, but in fact the SQL command that is sent to the server and successfully executed is the compound statement:

COPY a FROM STDIN CSV HEADER; end;

That does work, but when dealing with the results of compound statements, psql only handles the last one, as mentioned in Backslash-semicolon:

psql prints only the last query result it receives for each request

The expected COPY 2 command tag is not displayed, because it's the first of two results of a compound statement: COPY and COMMIT.

Workarounds

The simplest one is probably to use -1 or --single-transaction as an argument to psql to make it enclose the sequence of commands inside a transaction:

echo "delete from a; \copy a from a.csv CSV HEADER" | psql -1 [other options]

With this form, the end of the line is clear so \copy can consume it. Plus that's why -1 is made for.

Another option if you had to put other commands after the \copy anyway: use the heredoc syntax:

$ psql [options] << ENDOFCOMMANDS
begin;
delete from a;
\copy a from a.csv CSV HEADER
-- other commands
end;
ENDOFCOMMANDS
Related Question