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

Question

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?

Answer 1

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

Answer 2

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