你能否根据列值将Postgres表导出为多个CSV文件？

Question

我在Postgres中有一张约600万行的表，想要将它们拆分并导出为多个CSV文件。是否有一种方法可以根据一行数据的列值自动生成不同的CSV文件？

- Paul

---

你是在使用COPY吗？你有阅读文档吗？ - mu is too short

我见过COPY，但不幸的是，还需要更多的命令来根据列的值决定拆分。 - Paul

@Paul COPY (SELECT ...)？ - Craig Ringer

@CraigRinger 是的，但有很多不同的列值，因此手动创建复制语句可能会比较困难。 - Paul

Answer 1

一般来说，您需要使用 COPY (SELECT ...)。

一种选择是使用PL/PgSQL和EXECUTE。类似以下方式：

DO
LANGUAGE plpgsql
$$
DECLARE
    colval integer;
BEGIN
    FOR colval IN SELECT DISTINCT thecol FROM thetable
    LOOP
       EXECUTE format('COPY (SELECT * FROM thetable WHERE colval = %L) TO ''/tmp/out-%s.csv'';', colval, colval);
    END LOOP;
END;
$$

另一种方法是编写一个使用 psql 和 \copy 的脚本。

另一种方法是使用你喜欢的客户端语言及其对 COPY 的支持，例如 PgJDBC 的 CopyManager，Python+psycopg2 的 copy_to 等。

更新: 我刚刚意识到这比想象中简单。按目标列排序并在处理文件流时拆分它。以下是使用 psql、bash 和 awk 的示例：

CREATE TABLE demo(
  id serial primary key,
  targetcol integer not null
);

-- Create 10 distinct values for targetcol with 100 entries each
insert into demo(targetcol)
select x
from generate_series(1,10) x cross join generate_series(1,100) y;

将第二列视为文件名的一部分，切换文件以输出记录：

psql -At -c '\copy (SELECT * FROM demo ORDER BY targetcol) TO stdout' | \
awk '
  BEGIN {
    prev_col=0;
    cur_file="";
  } 
  {
    if ($2 != prev_col) {
      prev_col = $2;
      if (cur_file != "") {
        close(cur_file);
      }
      cur_file = sprintf("outfile-%d",$2);
      printf "" > cur_file;
    } 
    print $0 >> cur_file; 
  }
';

实际上，如果您不介意稍微慢一些并且有可能会因目标列的许多值而耗尽最大打开文件数，即使您不需要排序输入，也可以执行此操作：

psql -At -c '\copy demo TO stdout' | \
awk '
  BEGIN {
    cur_file="";
  } 
  {
    print $0 >> sprintf("outfile-%d",$2); 
  }
';

- Craig Ringer

---

@Paul 编辑后提供了一个使用 bash 和 awk 的完整解决方案。一旦我想到了，它就非常简单，并且应该非常快。 - Craig Ringer

Answer 2

有几种方法可以做到这一点。我无法想到一种自动执行单个命令的方法。我不知道您使用的操作系统是什么，或者您是否想在存储过程中执行此操作等等。如果我要从命令行快速而简单地执行此操作，我会执行以下步骤:

$ # bash shell here.
$ for i in `psql -Upostgres -h HOSTIP -Atq DBNAME -c 'select distinct COLNAME from TABLENAME'`; do
$   echo 'working on ': $i
$   cmd="select * from TABLENAME where COLNAME = '$i'"
$   psql -Upostgres -h HOSTIP -Atq DBNAME -c "copy ( $cmd ) to stdout with delimiter ','" > /tmp/$i
$ done

您需要提供以下信息： HOSTIP（如果您可以默认正确连接，则可以省略-h HOSTIP） DBNAME：包含数据的数据库 TABLENAME：拥有6MM行的表的名称 COLNAME：指定要将数据复制到其中的文件名称的列的名称

结果是一堆文件，其中包含表内容的逗号分隔片段，位于/tmp目录中。

这应该会给您一些想法。我想您的问题的答案是否定的，没有“自动”的方法。祝你好运！

-g

- Greg

---

是的，我猜在纯SQL中做起来可能并不容易。我想使用存储过程或其他程序可能是最好的方法。感谢您的回答！ - Paul