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.
Add a comment
|
6 Answers
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/
8 Comments
Arihant Godha
Want this exact solution but Not working for me giving could not open file "/home/user/Documents/public.activities.csv" for writing: Permission denied
tarikki
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 :)
jllodra
@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 :).
unom
@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. stackoverflow.com/a/37210706
clops
Thank you, saved my day! Had to export 600+ tables :D
|
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
1 Comment
Eevee
This deserves fewer upvotes, since it doesn't answer the question?
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;
Comments
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>
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.
