I would like to export a single Postgres table's data into a .csv file. Can anyone give me an example of how to do that?
Asked
Active
Viewed 1.1e+01k times
63
-
1Possible duplicate http://stackoverflow.com/questions/1120109/export-postgres-table-to-csv-file-with-headings – o3o May 23 '12 at 09:52
-
Possible duplicate of [Export Postgres table to CSV file with headings](http://stackoverflow.com/questions/1120109/export-postgres-table-to-csv-file-with-headings) – Ethan Furman Jan 19 '17 at 20:52
3 Answers
85
In psql:
\copy tablename to 'filename' csv;
Peter Eisentraut
- 35,221
- 12
- 85
- 90
-
12Just for completeness - how would someone import this on the other end? – Nathan Osman May 06 '14 at 02:14
-
http://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgres-table – Budhapest Feb 02 '15 at 22:15
-
And where this file is stored?, cuz I want to export it in my localhost server. – Mohammed Khurram Jul 12 '21 at 09:35
63
First, log into the PostgreSQL console via the command line with the psql command.
To export:
\connect database_name;
\copy my_table TO 'my_table.csv' CSV;
\q
To import:
\connect database_name;
\copy my_table FROM 'my_table.csv' DELIMITER ',' CSV;
\q
Done!
Or, from a shell script!
export PGPASSWORD=dbpass
psql --dbname=mydb --username=dbuser --host=127.0.0.1 -c "COPY (SELECT * FROM widget) TO stdout DELIMITER ',' CSV HEADER" > export.csv
Bonus Advice Use pgcli, it's way better than psql
Nick Woodhams
- 11,977
- 10
- 50
- 52
-
2Plus 1 for pointing to pgcli, that is a cool and useful command line client – mnd May 17 '19 at 20:55
-
2
12
When logged into psql:
COPY tablename TO 'filename';
For more details, see this: http://www.postgresql.org/docs/current/static/sql-copy.html
-
3And that will copy the data to a server-accessible file. Which may or may not be the goal. For a client-side copy (inside `psql`) use `\copy`. – Milen A. Radev Jan 25 '11 at 09:15
-
6