1

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?

  • Try to use '\\' separator meta-command: echo "delete from a; \\ \copy a from a.csv CSV HEADER" | psql -hlocalhost -dpostgres -Upostgres – Akina Feb 20 at 9:22
  • @Akina I've tried using '\\' it's still not working. Interestingly, this works - echo -e "begin;\ndelete from a;\n\\\copy a from a.csv CSV HEADER;\nend;" | psql -hlocalhost -dpostgres -Upostgres. I'm flummoxed to start using \n characters in my scripts. Is there any other workaround? – sandeshyapuram Feb 20 at 9:54
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.