CODEWITHSUNDEEP
macosbashpostgresqlpsql
user606521
user606521

Reputation: 15464

How to pass SQL query to psql as a argument containing double quotes

I am trying to:

psql -c "COPY ( SELECT * FROM "Users" LIMIT 10 ) TO STDOUT WITH CSV HEADER" > out.csv

However double quotes inside query ("Users") are removed and psql returns error that Relation users does not exist. I tried to escape quotes like this \"Users\". but they are still removed. What can I do?

Upvotes: 10

Views: 5734

Answers (2)

user606521
user606521

Reputation: 15464

And here is the right answer (it works for me):

echo 'COPY ( SELECT * FROM "Users" LIMIT 10 ) TO STDOUT WITH CSV HEADER' | psql > out.csv

And even better (it allows to use single and double quotes without any escaping):

psql > out.csv <<EOT
COPY
(SELECT id, email, "displayName", "firstName", "lastName", "displayName", 'some str' AS "someStr" FROM "Users" LIMIT 10)
TO STDOUT WITH CSV HEADER;
EOT

Upvotes: 12

triggerNZ
triggerNZ

Reputation: 4771

psql -c 'COPY ( SELECT * FROM "Users" LIMIT 10 ) TO STDOUT WITH CSV HEADER' > out.csv

In bash, you can use single quotes in strings provided you don't need to interpolate anything. Of course if your query contained single quotes, that wouldn't work but in your case you should be fine

Upvotes: 6

Related Questions