Export Postgres Database into CSV file

Question

I want to export a Postgres database into a CSV file. Is this possible?

If it is possible, then how can I do this? I have seen that we can convert a particular table into a CSV file but I don't know about a whole database.

Answer 1

I made this pl/pgsql function to create one .csv file per table (excluding views, thanks to @tarikki):

CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$
declare
   tables RECORD;
   statement TEXT;
begin
FOR tables IN 
   SELECT (table_schema || '.' || table_name) AS schema_table
   FROM information_schema.tables t INNER JOIN information_schema.schemata s 
   ON s.schema_name = t.table_schema 
   WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema')
   AND t.table_type NOT IN ('VIEW')
   ORDER BY schema_table
LOOP
   statement := 'COPY ' || tables.schema_table || ' TO ''' || path || '/' || tables.schema_table || '.csv' ||''' DELIMITER '';'' CSV HEADER';
   EXECUTE statement;
END LOOP;
return;  
end;
$$ LANGUAGE plpgsql;

And I use it this way:

SELECT db_to_csv('/home/user/dir');
-- this will create one csv file per table, in /home/user/dir/

Answer 2

You can use this at psql console:

\copy (SELECT foo,bar FROM whatever) TO '/tmp/file.csv' DELIMITER ',' CSV HEADER

Or it in bash console:

psql -P format=unaligned -P tuples_only -P fieldsep=\, -c "SELECT foo,bar FROM whatever" > output_file

Answer 3

Modified jlldoras brilliant answer by adding one line to prevent the script from trying to copy views:

CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$
declare
   tables RECORD;
   statement TEXT;
begin
FOR tables IN 
   SELECT (table_schema || '.' || table_name) AS schema_table
   FROM information_schema.tables t INNER JOIN information_schema.schemata s 
   ON s.schema_name = t.table_schema 
   WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema', 'configuration')
   AND t.table_type NOT IN ('VIEW')
   ORDER BY schema_table
LOOP
   statement := 'COPY ' || tables.schema_table || ' TO ''' || path || '/' || tables.schema_table || '.csv' ||''' DELIMITER '';'' CSV HEADER';
   EXECUTE statement;
END LOOP;
return;  
end;
$$ LANGUAGE plpgsql;

Answer 4

If you want to specify the database and user while exporting you can just modify the answer given by Piotr as follows

psql -P format=unaligned -P tuples_only -P fieldsep=\, -c "select * from tableName" > tableName_exp.csv -U <USER> -d <DB_NAME>

Answer 5

Do you want one big CSV file with data from all tables?

Probably not. You want separate files for each table or one big file with more information that can be expressed in CSV file header.

Separate files

Other answers shows how to create separate files for each table. You can query database to show you all tables with such query:

SELECT DISTINCT table_name
FROM information_schema.columns
WHERE table_schema='public'
AND position('_' in table_name) <> 1
ORDER BY 1

One big file

One big file with all tables in CSV format used by PostgreSQL COPY command can be created with pg_dump command. Output will also have all CREATE TABLE, CREATE FUNCTION etc, but with Python, Perl or similar language you can easily extract only CSV data.

Answer 6

I downloaded a copy of RazorSQL, opened the database server and right-clicked on the database and selected Export Tables and it gave me the option of CSV, EXCEL, SQL etc...