Handling quotes in COPY to CSV from command shell

Question

I am trying to run this COPY query from the command shell:

COPY products_273 TO '/tmp/products_199.csv' DELIMITER ',' CSV HEADER;

I know you can run queries from the command line using psql:

psql -U username -d mydatabase -c 'SELECT * FROM mytable'

But how can I combine the 2? The below doesn't seem to work but I'm not sure how/if I need to escape.

psql -U username -d mydatabase -c 'COPY products_273 TO '/tmp/products_199.csv' DELIMITER ',' CSV HEADER;'

Which "command shell", important missing detail. – Erwin Brandstetter Jul 04 '16 at 22:40 — Erwin Brandstetter, Jul 04 '16 at 22:40

score 2 · Accepted Answer · answered Jul 04 '16 at 21:34

2

Don't mix ' and ", quote query with ".

psql -U username -d mydatabase -c "COPY products_273 TO '/tmp/products_199.csv' DELIMITER ',' CSV HEADER;"

answered Jul 04 '16 at 21:34

ferhatelmas

3,818
1
21
25

wildplasser · Answer 2 · 2016-07-04T22:48:54.933

2

Simplest solution is (IMHO)to use a here-documant. This will also solve (almost) all your quoting problems:

#!bin/sh
psql -U username -d mydatabase <<OMG
  COPY products_273 TO '/tmp/products_199.csv' DELIMITER ',' CSV HEADER;
OMG

edited Jul 04 '16 at 22:48

answered Jul 04 '16 at 22:20

wildplasser

43,142
8
66
109

score 2 · Answer 3 · edited May 23 '17 at 12:32

^{Assuming a UNIX(-oid) shell.}

If you can't use a here document like @wildplasser provided I suggest dollar quotes for string literals within your SQL command. Replacing the surrounding single quotes with double quotes would turn on variable expansion in the shell, which may have side effects if special characters are contained: $, ` (backquote), and \.

psql -U username -d mydatabase -c 'COPY products_273 TO $$/tmp/products_199.csv$$ DELIMITER $$,$$ CSV HEADER;'

With full quoting in the shell (single quotes) the string is passed as is, no special meaning for any character.

If special characters in the SQL string can be ruled out or are dealt with properly, @ferhat's simple solution will do fine.

Or you can replace each single quote (') within the fully quoted string with '\'' - which effectively ends the quoted string, appends a literal ' and starts a new full quote.

psql -U username -d mydatabase -c 'COPY products_273 TO '\''/tmp/products_199.csv'\'' DELIMITER '\'','\'' CSV HEADER;'

How to escape single-quotes within single-quoted strings?

Or put the SQL command in a separate file (without surrounding quotes) and use psql -f:

psql -U username -d mydatabase -f /path/to/myfile.sql

I assume you are aware of the difference between \copy (the psql meta-command) and COPY (the SQL command). \copy reads and writes files local to the client, while COPY does the same for files on the server. If client and server run on the same installation, you can use either. Certain differences apply, though.

Problems while importing a txt file into postgres using php

Handling quotes in COPY to CSV from command shell

3 Answers3