using COPY command with SQL query

[AP81]

Hi,

I am trying to perform a copy using a query.

This works for example:

psql -h localhost -U admin suppliers -c 'COPY connections TO STDOUT WITH CSV' > /tmp/suppliers.csv

This on the other hand doesn't work:

psql -h localhost -U admin suppliers -c 'COPY (select id, latitude, longitude from connections order by id) TO STDOUT WITH CSV' > /tmp/suppliers.csv

Any ideas how to get this to work?

 

[feherke]

Hi

That is not possible. The [tt]copy[/tt] statement's parameter must be a table.

Probably your best choice is to create a temporary table :

psql -h localhost -U admin suppliers -c 'create table whatever as select id, latitude, longitude from connections order by id; COPY whatever TO STDOUT WITH CSV; drop table whatever' > /tmp/suppliers.csv

Or you can format abit the output, but you will have problem with text values containing newline ( \n ) characters :

psql -d suppliers -h localhost -U admin -t -A -F $'\t' -P null='\N' -c 'select id, latitude, longitude from connections order by id'

Feherke.

http://rootshell.be/~feherke/