Converting PostgreSQL dump file into CSV files

Question

I have a PostgreSQL database running in a docker container. I used the below command to get a dump file of the Postgres database - "docker exec -t your-db-container pg_dumpall -c -U postgres > dump_date +%d-%m-%Y"_"%H_%M_%S.sql". Since it's a docker volume , the database doesn't store on the machine to use psql COPY command and such.

I have multiple tables with different headers. I need to convert the dump file into a CSV file with the headers of the table. Can be a single or multiple CSV files.

Any other way of directly getting the CSV files from the docker container database will also be appreciated.

PS: I'm a beginner in this area and would really appreciate a detailed solution

Answer 1

You have to use 'COPY' command,try this:

COPY (SELECT * from table) To '/path/output.csv' With CSV;

Answer 2

Using pg_dump with the -t flag could provide a plain text output for a table and you can increase the tables you need by adding that flag as much as needed. On the other hand getting a CSV output is only available via psql flag --csv.

So you can try

psql -d your_db -c 'select * from your_table' --csv 

for your need.