How to export table data to file
PostgresqlPostgresql Problem Overview
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?
Postgresql Solutions
Solution 1 - Postgresql
In psql:
\copy tablename to 'filename' csv;
Solution 2 - Postgresql
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
Solution 3 - Postgresql
When logged into psql:
COPY tablename TO 'filename';
For more details, see this: http://www.postgresql.org/docs/current/static/sql-copy.html