database – 将postgres表导出为csv错误

发布时间：2020-12-13 15:50:37 所属栏目：百科来源：网络整理

导读：我试图将我所有的postrgres表导出到单独的csv文件中,因为我正在使用以下函数 CREATE OR REPLACE FUNCTION db_to_csv(path text) RETURNS void AS$BODY$declare tables RECORD; statement TEXT;begin FOR tables IN SELECT (table_schema || '.' || table_nam

我试图将我所有的postrgres表导出到单独的csv文件中,因为我正在使用以下函数

CREATE OR REPLACE FUNCTION db_to_csv(path text)
  RETURNS void AS
$BODY$
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')
    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;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION db_to_csv(text)
  OWNER TO postgres;

但当我调用此函数时,我无法打开文件“/home/user/Documents/public.tablename.csv”进行写入：权限被拒绝

我曾尝试使用复制单个表

COPY activities TO '/home/user/Documents/foldername/conversions/tablename.csv' DELIMITER ',' CSV HEADER;

它给了我以下错误

ERROR:  could not open file "/home/user/Documents/foldername/conversions/tablename.csv" for writing: Permission denied


********** Error **********

 ERROR:  could not open file "/home/user/Documents/foldername/conversions/tablename.csv" for writing: Permission denied
SQL state: 42501

任何建议如何解决这个问题.

解决方法

创建一个每个用户都有权访问的文件夹.然后在那里的文件上运行COPY命令. COPY仅适用于postgres用户有权访问的目录

sudo mkdir /media/export
sudo chmod 777 /media/export

COPY activities TO '/media/export/activities.csv' DELIMITER ',' CSV HEADER;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!