postgresql 将数据库导出为 CSV 文件
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/17463299/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export Database into CSV file
提问by Anand Singh
I want to export a database into a CSV file. Is this possible?
我想将数据库导出为 CSV 文件。这可能吗?
If it is possible, then how can I do this using PostgreSQL? I have seen that we can convert a particular table into a CSV file but I don't know about a whole database.
如果可能,那么我如何使用 PostgreSQL 做到这一点?我已经看到我们可以将特定表转换为 CSV 文件,但我不知道整个数据库。
回答by jllodra
I made this pl/pgsql function to create one .csv file per table(excluding views, thanks to @tarikki):
我制作了这个 pl/pgsql 函数来为每个表创建一个 .csv 文件(不包括视图,感谢@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/
回答by Piotr Olaszewski
You can use this at psql console:
您可以在 psql 控制台使用它:
\copy (SELECT foo,bar FROM whatever) TO '/tmp/file.csv' DELIMITER ',' CSV HEADER
Or it in bash console:
或者在 bash 控制台中:
psql -P format=unaligned -P tuples_only -P fieldsep=\, -c "SELECT foo,bar FROM whatever" > output_file
回答by tarikki
Modified jlldoras brilliant answer by adding one line to prevent the script from trying to copy views:
通过添加一行以防止脚本尝试复制视图来修改 jlldoras 精彩答案:
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;
回答by Tombeau
COPY tablename TO '/tmp/products_199.csv' DELIMITER ',' CSV HEADER;
check this out
看一下这个
回答by smishra
If you want to specify the database and user while exporting you can just modify the answer given by Piotr as follows
如果你想在导出时指定数据库和用户,你可以修改 Piotr 给出的答案如下
psql -P format=unaligned -P tuples_only -P fieldsep=\, -c "select * from tableName" > tableName_exp.csv -U <USER> -d <DB_NAME>
回答by Robs
回答by Micha? Niklas
Do you want one big CSV file with data from all tables?
你想要一个包含所有表数据的大 CSV 文件吗?
Probably not. You want separate files for each table or one big file with more information that can be expressed in CSV file header.
可能不是。您需要为每个表或一个大文件使用单独的文件,其中包含可以在 CSV 文件标题中表示的更多信息。
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.
COPY
可以使用pg_dump
命令创建一个包含PostgreSQL命令使用的所有 CSV 格式表的大文件。输出也将拥有所有CREATE TABLE
,CREATE FUNCTION
等等,但与Python,Perl或类似的语言,你可以很容易地只提取CSV数据。