Search code examples
databasepostgresqlpsqlpg-restore

How to extract a table from a *.dump file into a CSV

I have a *.dump file (postgresql dump) and I would like to output my_table to my_table.csv. Is there a better way to do this than pg_restore -t my_table db.dump > my_table.txt and then writing a script to create the CSV from the output?

Solution

  • The output from pg_restore --data-only -t my_table db.dump basically is tab-separated headerless tabulated text with some comments and a few extra commands. A script to mangle it into csv with a tool like perl or awk would be pretty simple.

    That said, personally I would:

    • Restore the table to a temporary database created for the purpose. If the table depends on custom types, functions, sequences, etc you will need to restore them too.

    • In psql, \copy the_table TO 'some_file.csv' WITH (FORMAT CSV, HEADER ON)

    This way you can control the representation of nulls and lots more.