Postgresql: run SQL query script and export to csv

Question

I am running Postgresql version 9.3 on Ubuntu. I am wondering if there is a way to run a select statement stored in a script on a Postgres database and then send the results to csv. I know how to do either of the two by themselves; for example, exporting to csv (with the query explicitly written out):

COPY(select * from mytable) to 'path/to/file.csv' with csv header;

and running an sql script:

\i 'myscript.sql'

But after searching around I could not figure out how to do both in one action. That is, I could not figure out how to export to a csv when running a query script rather than typing out a select statement manually.

could you try explaining what you want a little better

Evan Carroll
– Evan Carroll

2017-04-19 20:04:30 +00:00
Commented Apr 19, 2017 at 20:04 — Evan Carroll
– Evan Carroll, Commented Apr 19, 2017 at 20:04

Clodoaldo Neto · Accepted Answer · 2017-04-19 20:15:15Z

3

\set query `cat /path/to/myscript.sql`
copy (:query) to '/path/to/file.csv' with csv header;

SQL Interpolation in psql

answered Apr 19, 2017 at 20:15

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

Tun Over a year ago

make sure you do not have ; in myscript.sql and you have write permission to the folder where csv file will be exported.

Collectives™ on Stack Overflow

Postgresql: run SQL query script and export to csv

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related