postgresql - 使用 PostgreSQL COPY 时为 "ERROR: extra data after last expected column"

Question

请耐心等待，因为这是我的第一篇文章。

我正在尝试运行 COPY PostgreSQL-9.2 中用于将制表符分隔表从 .txt 文件添加到 PostgreSQL 数据库的命令，例如:

COPY raw_data FROM '/home/Projects/TestData/raw_data.txt' WITH (DELIMITER ' ');

我已经使用 SQL 命令在数据库中创建了一个名为“raw_data”的空表:

CREATE TABLE raw_data ();

尝试运行 COPY 命令时，我不断收到以下错误消息:

ERROR:  extra data after last expected column
CONTEXT:  COPY raw_data, line 1: "  1   2   3   4   5   6   7   8   9   10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31  32  33  34  35  36  ..."

(这里的数字应该是列标题)

我不确定是不是因为我在创建数据库表时没有指定表列，但我试图避免必须手动输入 800 或列。

关于如何解决这个问题有什么建议吗？

这是 .txt 文件的示例:

        1   2   3   4   5   6   7   8   9
binary1 1   1   0   1   1   1   1   1   1
binary2 1   0   0   1   0   1   1   0   0
binary3 1   0   1   1   1   0   0   1   0
binary4 1   1   1   1   0   1   0   1   0

Answer 1

空表不行。您需要与输入数据结构相匹配的表。像这样的东西:

CREATE TABLE raw_data (
  col1 int
, col2 int
  ...
);

您不需要将 tab 声明为 DELIMITER，因为这是默认设置:

COPY raw_data FROM '/home/Projects/TestData/raw_data.txt';

你说800列？如此多的列通常表明您的设计存在问题。不管怎样，有一些方法可以使 CREATE TABLE 脚本半自动化。

自动化

假设简化的原始数据

1   2   3   4  -- first row contains "column names"
1   1   0   1  -- tab separated
1   0   0   1
1   0   1   1

定义一个不同的DELIMITER(一个根本不会出现在导入数据中的)，并导入到具有单个text列的临时暂存表:

CREATE TEMP TABLE tmp_data (raw text);

COPY tmp_data FROM '/home/Projects/TestData/raw_data.txt' WITH (DELIMITER '§');

此查询创建 CREATE TABLE 脚本:

SELECT 'CREATE TABLE tbl (col' || replace (raw, E'\t', ' bool, col') || ' bool)'
FROM   (SELECT raw FROM tmp_data LIMIT 1) t;

更通用和更安全的查询:

SELECT 'CREATE TABLE tbl('
    ||  string_agg(quote_ident('col' || col), ' bool, ' ORDER  BY ord)
    || ' bool);'
FROM  (SELECT raw FROM tmp_data LIMIT 1) t
     , unnest(string_to_array(t.raw, E'\t')) WITH ORDINALITY c(col, ord);

返回:

CREATE TABLE tbl (col1 bool, col2 bool, col3 bool, col4 bool);

验证有效性后执行 - 或者如果您相信结果则动态执行:

DO
$$BEGIN
EXECUTE (
   SELECT 'CREATE TABLE tbl (col' || replace(raw, ' ', ' bool, col') || ' bool)'
   FROM  (SELECT raw FROM tmp_data LIMIT 1) t
   );
END$$;

然后使用此查询INSERT数据:

INSERT INTO tbl
SELECT (('(' || replace(replace(replace(
                  raw
                , '1',   't')
                , '0',   'f')
                , E'\t', ',')
             || ')')::tbl).*
FROM   (SELECT raw FROM tmp_data OFFSET 1) t;

或使用 translate() 更简单:

INSERT INTO tbl
SELECT (('(' || translate(raw, E'10\t', 'tf,') || ')')::tbl).*
FROM   (SELECT raw FROM tmp_data OFFSET 1) t;

字符串被转换为行字面量，转换为新创建的表行类型并使用(row).* 分解。

全部完成。

您可以将所有这些放入一个 plpgsql 函数中，但您需要防止 SQL 注入(inject)。 (这里SO上有很多相关的解决方案，试试搜索吧。

db<> fiddle here
Old SQL Fiddle