I can never remember how to output to a CSV file from postgres, and end up having to google it time and time again – so I’m making a note of it here mostly for my own use 🙂
|
1 2 3 4 5 6 7 |
f ',' a t o /tmp/moocow.csv SELECT foo,bar FROM whatever; o q |
If a field has newlines, this will break. You can do something like this instead…..
|
1 |
SELECT foo, bar, '"' || REPLACE(REPLACE(field_with_newilne, 'n', '\n'), '"', '""') || '"' FROM whatever; |
After much pain with line endings:
COPY (SELECT foo,bar FROM whatever) TO ‘/tmp/dump.csv’ WITH CSV HEADER
thanks! much easier than trying to do it with copy and paste in the terminal!
10000 thanx very easy and worked.
I also can never remember how to do this, and I always google it, and end up here. So, thank you for keeping this handy!!!
Saves so much effort looking for a simple example!
I run a lot of queries from the bash command line or from scripts, so here’s a tip:
If you run into this error: “ERROR: must be superuser to COPY to or from a file,” try this:
echo “COPY (SELECT foo from BAR) TO STDOUT with CSV HEADER” | psql -o filename.csv database_name
@Ben,
Your solution works well in version 8.4.2 and it shows a syntax error in version 8.1
copy to file command in postgresql 8.1 does not accept a query . it can copy whole table but not a query
do u have any solution for 8.1?
try creating a view and using it instead of the query. hope this helps.
Your post was useful
Could you tell me how I can include the data in double quotes ?
o /tmp/moocow.csv
SELECT foo,bar FROM whatever;
o
and the output like
“foo”,”bar”
“fdata”,”bdata”
“fdata1″,”bdata2”
Appreciate your help !
This works
psql dbname -F , –no-align -c “SELECT * FROM table”
psql “dbname=dbname host=host user=user password=password port=port” -F , –no-align -c “select * from schema.table limit 10” > /path/sql_output.csv
I’ve tried this and output is coming with | (pipe) not with , (comma).
Where I’m doing the wrong ?
I have to export several queries from different tables but the COPY command always overwrites the file.
Is there any solution for this ?
thanks! Could you tell me how to replace ‘,’ by ‘.’ in the file CSV
please
@sonia,
You can do like this,
COPY (SELECT foo,bar FROM whatever) TO ‘/tmp/dump.csv’
delimiters ‘.’ WITH CSV HEADER
Bala
THANKS A LOT. I was spending time on this.
Very helpful, thanks!
Thank you for this tip. Saved me a lot of work
There is a far superior way of doing this which does not require admin rights (COPY does):
copy (select * from table) to ‘filename’ csv header
Refer to http://stackoverflow.com/questions/1120109/export-postgres-table-to-csv-file-with-headings for a list of options
Is there a way to modify the query below to output a filename with today’s date or current date (MMDDYY)? For example: filename_030414.csv (where 03=month, 04=day, 14=year)?
copy (select * from table) to ‘filename’ csv header
thanks, this saved me some time!
We can export postgres table using below query
Copy (SELECT tracklocationid, msisdnnumber, lat, lng, createdon
FROM tracklocation
where msisdnnumber= ‘9833922076’ and lat!=’0′
order by tracklocationid desc) To ‘D:/tracklocation.csv’ DELIMITER ‘,’ CSV HEADER;
for more visit http://www.etechpulse.com/2013/11/how-to-export-postgres-table-to-csv.html