将Postgres数据库导出为CSV文件

Question

我想将一个Postgres数据库导出为CSV文件，这是可能的吗？

如果可以的话，那么我该如何操作呢？我已经看到我们可以将特定的表转换成CSV文件，但是我不知道怎样才能处理整个数据库。

- Anand Singh

Answer 1

我创建了这个pl/pgsql函数来为每个表（不包括视图，感谢@tarikki）创建一个csv文件：

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;

我是这样使用它的：

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

- jllodra

---

我希望你能提供这个解决方案，但对我来说不起作用，提示“无法打开文件“/home/user/Documents/public.activities.csv”进行写操作：权限被拒绝”。 - Arihant Godha

这很漂亮。运作良好。谢谢！ - David Watson

了不起的脚本。我只是稍微修改了一下（添加了一行代码），这样它就不会尝试复制视图，并在下面发布了它。谢谢你，让我的老板很高兴 :) - tarikki

@unmircea 很难说。您应该检查当前用户（postgres）是否具有读取information_schema.tables和information_schema.schemata的数据库权限。还要检查您是否有创建和执行存储过程的权限。还要仔细检查运行postgresql的系统用户（通常在*nix系统上为postgres）是否可以写入目标目录。-啊，抱歉，刚刚看到您已经解决了。太好了。如果您不想使用/tmp，您也可以chmod或chown另一个文件夹。:) - jllodra

谢谢，你救了我的一天！必须导出600多个表格:D - clops

Answer 2

您可以在 psql 控制台中使用以下命令：

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

或在Bash控制台中：

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

- Piotr Olaszewski

---

这篇文章值得获得更少的赞，因为它没有回答问题。 - Eevee

Answer 3

在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;

- tarikki

Answer 4

如果您想在导出时指定数据库和用户，只需按照Piotr给出的答案进行修改即可。

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

- smishra

---

我很惊讶你的回答清晰有效，但点赞数不多。 - Jason Liu

谢谢，我相信这是答案呈现的顺序问题。 - smishra

Answer 5

你想要一个包含所有表格数据的大型CSV文件吗？

可能不是。你想要每个表格的单独文件或者一个包含更多信息的大型文件，这些信息可以在CSV文件头中表示。

单独的文件

其他答案展示了如何为每个表格创建单独的文件。你可以查询数据库以显示所有具有此类查询的表格：

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

一个大文件

可以使用pg_dump命令创建一个包含所有表格以CSV格式存储的大文件，该文件可用于PostgreSQL的COPY命令。输出还将包括所有的CREATE TABLE、CREATE FUNCTION等内容，但是使用Python、Perl或类似语言，您可以轻松提取只有CSV数据。

- Michał Niklas

Answer 6

我下载了一个RazorSQL的副本，打开了数据库服务器并右键单击数据库，选择导出表，然后它给了我CSV、EXCEL、SQL等选项......

- Robs