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.

jllodra · Answer 1 · 2017-02-14T16:11:37.540

67

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/

edited Feb 14 '17 at 16:11

answered Nov 18 '13 at 20:10

jllodra

1,348
1
14
22

4

Want this exact solution but Not working for me giving could not open file "/home/user/Documents/public.activities.csv" for writing: Permission denied – Arihant Godha May 23 '14 at 05:05
Seems obvious that the user doesn't have permission to write on that path. Try with another path owned by the user who is executing the script. – jllodra May 23 '14 at 09:53
@ArihantGodha try changing `COPY` to `\COPY` – dayer4b Aug 18 '15 at 13:56
4

This is beautiful. Works great. Thanks! – David Watson Aug 29 '15 at 00:57
3

Brilliant script. I just modified it a bit (added one line) so it doesn't try to copy views and posted it below. Thanks for this, made my boss happy :) – tarikki May 13 '16 at 12:54
I launched "psql -U postgres" and no matter that path I enter I get "Permission denied". How can I fix this? fixed with SELECT db_to_csv('/tmp'); – unom Feb 02 '17 at 16:18
2

@unmircea Pretty difficult to say. You should check if your current user (postgres) has database privileges to read `information_schema.tables` and `information_schema.schemata`. Also, check if you have permissions to create and execute stored procedures. Also double check that the system user running postgresql (generally `postgres` on a *nix system) can write on the destination directory. – Ah, sorry, just read that you fixed it. Great. You can `chmod` or `chown` another folder too if you don't want to use `/tmp` :). – jllodra Feb 08 '17 at 16:29
1

@jllodra Apparently the "Permission denied" issue was because the above script tries to export Views too... Used the script in the answer below and got it working. http://stackoverflow.com/a/37210706 – unom Feb 10 '17 at 19:19
4

Thank you, saved my day! Had to export 600+ tables :D – clops May 24 '17 at 15:20

score 57 · Answer 2 · answered Jul 04 '13 at 06:33

57

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

answered Jul 04 '13 at 06:33

Piotr Olaszewski

6,017
5
38
65

14

This deserves fewer upvotes, since it doesn't answer the question? – Eevee Oct 18 '16 at 06:07

score 19 · Answer 3 · answered May 13 '16 at 12:58

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;

score 7 · Answer 4 · answered May 28 '15 at 16:44

7

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>

answered May 28 '15 at 16:44

smishra

3,122
29
31

I am surprise that your answer is clear and works well but not much up votes. – Jason Liu Mar 13 '20 at 16:22
Thanks, I believe it's the order in which answers are presented. – smishra Mar 14 '20 at 00:02

score 0 · Answer 5 · answered Jul 04 '13 at 08:00

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.

score 0 · Answer 6 · answered Jul 17 '18 at 15:48

0

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...

answered Jul 17 '18 at 15:48

Robs

8,219
6
41
57

Export Postgres Database into CSV file

6 Answers6

Linked