16

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.

2 Answers 2

33

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
1
  • 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
    – Tom Paine
    Commented Sep 19, 2023 at 18:07
7

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
1
  • 1
    In my shell (bash 3) I had to export PGDATABASE=...
    – Jared Beck
    Commented Jul 22, 2020 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.