CODEWITHSUNDEEP
sqlpostgresqlshellpsqlpostgresql-copy
Henley Wing Chiu
Henley Wing Chiu

Reputation: 22515

Handling quotes in COPY to CSV from command shell

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;'

Upvotes: 0

Views: 1130

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

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;'

Related:

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.

Upvotes: 2

wildplasser
wildplasser

Reputation: 44230

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

Upvotes: 2

ferhatelmas
ferhatelmas

Reputation: 3978

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

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

Upvotes: 2

Related Questions