How to create CSV file from SQL?

Question

I am wondering how to create or export a CSV file from SQL? Is there any function for that similar to pgsql2shp? I would appreciate your ideas, tip or solutions.

MichielB · Accepted Answer · 2016-09-17 11:25:52Z

6

You can save a complete table as a file using this command:

COPY tablename TO STDOUT CSV

Ref: https://www.postgresql.org/docs/current/static/sql-copy.html

edited Sep 17, 2016 at 11:25

MichielB

4,3441 gold badge33 silver badges39 bronze badges

answered Jun 11, 2010 at 13:24

vartec

135k38 gold badges227 silver badges248 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gruntled · Accepted Answer · 2010-06-11 13:25:03Z

4

You can give this a try. But i believe there may be some syntax changes depending on the version.

COPY (SELECT foo,bar FROM whatever) TO ‘/tmp/dump.csv’ WITH CSV HEADER

answered Jun 11, 2010 at 13:25

gruntled

2,70419 silver badges16 bronze badges

2 Comments

Z77 Over a year ago

I got this message: ERROR: could not open file "/dump.csv" for writing: Permission denied What should i do now? Btw, instead of the query above i wrote: COPY (SELECT * FROM table1) TO '/dump.csv' WITH CSV HEADER because proposed path does not exist.

Z77 Over a year ago

Ok. I found out that problem was in path. So I changed path to: 'C:/Program Files/PostgreSQL/8.4/data/test.csv' and it works now. Obvoiusly some folders has restrictions.

Federico Cristina · Accepted Answer · 2010-06-12 21:28:28Z

1

If you use pgAdmin, you can export any query you run to a CSV file.

answered Jun 12, 2010 at 21:28

Federico Cristina

2,2431 gold badge19 silver badges38 bronze badges

Collectives™ on Stack Overflow

How to create CSV file from SQL?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related