将Postgres数据库导出到CSV文件

问题描述

我想将Postgres数据库导出到CSV文件中.这可能吗?

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

如果可能的话，我该怎么做?我已经看到我们可以将特定的表转换为CSV文件，但是我不知道整个数据库.

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.

推荐答案

我使用此pl/pgsql函数创建了每个表一个.csv文件(不包括视图，感谢@tarikki):

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;

我以这种方式使用它:

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

这篇关于将Postgres数据库导出到CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！