How can I dump all tables to CSV for a PostgreSQL schema?

Question

I have a database with a lot of schemas in it and I want to dump the each of the table contents to CSV. I'm aware of the COPY command but I'm not sure how to script something that will read all of the tables in a schema and execute the COPY against them.

Answer 1

Here's a shell script that can do what you want:

SCHEMA="myschema"
DB="mydb"
USER="myuser"
psql -U $USER -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" $DB |\
 while read TBL; do
 psql -U $USER -c "COPY $SCHEMA.$TBL TO STDOUT WITH CSV" $DB > $TBL.csv
 done

Make sure you set the DB and SCHEMA variables to your particular database and schema.

The wrapping psql command uses the A and t flags to make a list of tables from the string passed to the c command.

If you want to include table column names add "HEADER" at the end of the query:

psql -U $USER -c "COPY $SCHEMA.$TBL TO STDOUT WITH CSV HEADER" $DB > $TBL.csv

• Tom Paine: My tables had hyphens in which was causing errors. Quoting TBL solved it psql -U $USER -c "COPY $SCHEMA.\"$TBL\" TO STDOUT WITH CSV" $DB > $TBL.csv

Answer 2

If you need to export all schemas, here's the script

export PGDATABASE="db"
export PGUSER="user"
psql -Atc "select schema_name from information_schema.schemata" |\
 while read SCHEMA; do
 if [[ "$SCHEMA" != "pg_catalog" && "$SCHEMA" != "information_schema" ]]; then
 psql -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" |\
 while read TBL; do
 psql -c "COPY $SCHEMA.$TBL TO STDOUT WITH CSV DELIMITER ';' HEADER ENCODING 'UTF-8'" > $SCHEMA.$TBL.csv
 done
 fi
 done

• Jared Beck: In my shell (bash 3) I had to export PGDATABASE=...