主页 / dba / 问题 / 122120
Accepted
Joshua Burns
Asked: 2015-11-26 10:03:26 +0800 CST

PostgreSQL中主键的重复行

  • 772

假设我有一个名为 的表people，其中id是主键：

+-----------+---------+---------+
|  id       |  fname  |  lname  |
| (integer) | (text)  | (text)  |
+===========+=========+=========+
|  1        | Daniel  | Edwards |
|  2        | Fred    | Holt    |
|  3        | Henry   | Smith   |
+-----------+---------+---------+

我正在尝试编写一个足够健壮的行重复查询，以考虑对表的架构更改。每当我向表中添加一列时，我都不想返回并修改重复查询。

我知道我可以做到这一点，这将重复记录 id 2 并为重复的记录提供一个新的 id：

INSERT INTO people (fname, lname) SELECT fname, lname FROM people WHERE id = 2;

但是，如果我添加一age列，我需要修改查询以考虑年龄列。

显然我不能执行以下操作，因为它还会复制主键，从而导致duplicate key value violates unique constraint-- 而且，我不希望它们共享相同的 id：

INSERT INTO people SELECT * FROM people WHERE id = 2

话虽如此，解决这一挑战的合理方法是什么？我宁愿远离存储过程，但我不是 100% 反对它们，我想......

postgresql postgresql-9.4

3 个回答

  1. Best Answer

    简单的hstore

    如果您安装了附加模块hstore（下面链接中的说明），则有一种非常简单的方法可以在不了解其他列的情况下替换单个字段的值：

    基本示例：使用复制行id = 2但替换2为3：

    INSERT INTO people
SELECT (p #= hstore('id', '3')).* FROM people p WHERE id = 2;

    细节：

    假设（因为它没有在问题中定义）这people.id是一个serial带有附加序列的列，您将需要序列中的下一个值。我们可以用 来确定序列名称pg_get_serial_sequence()。细节：

    或者，如果序列名称不会改变，您可以硬编码序列名称。
    我们会有这样的查询：

    INSERT INTO people
SELECT (p #= hstore('id', nextval(pg_get_serial_sequence('people', 'id'))::text)).*
FROM people p WHERE id = 2;

    哪个有效，但 Postgres 查询计划器存在一个弱点：表达式是针对行中的每一列单独评估的，浪费了序列号和性能。为避免这种情况，请将表达式移动到子查询中并仅将行分解一次：

    INSERT INTO people
SELECT (p1).*
FROM  (
   SELECT p #= hstore('id', nextval(pg_get_serial_sequence('people', 'id'))::text) AS p1
   FROM   people p WHERE id = 2
   ) sub;

    一次（或几行）可能最快。

    json/jsonb

    如果您没有hstore安装并且无法安装其他模块，您可以使用json_populate_record()or执行类似的技巧jsonb_populate_record()，但该功能未记录并且可能不可靠。更新：自 Postgres 13 起也记录了该功能。请参阅：

    临时临时表

    另一个简单的解决方案是使用像这样的临时临时：

    BEGIN;
CREATE TEMP TABLE people_tmp ON COMMIT DROP AS
SELECT * FROM people WHERE id = 2;
UPDATE people_tmp SET id = nextval(pg_get_serial_sequence('people', 'id'));
INSERT INTO people TABLE people_tmp;
COMMIT;

    我添加ON COMMIT DROP了在事务结束时自动删除表。因此，我还将操作包装到它自己的事务中。两者都不是绝对必要的。

    这提供了广泛的附加选项 - 您可以在插入之前对行执行任何操作，但由于创建和删除临时表的开销，它会有点慢。

    此解决方案同时适用于单行或任意数量的行。每行自动从序列中获取一个新的默认值。

    使用短（SQL 标准）表示法TABLE people。

    动态 SQL

    一次处理多行，动态 SQL 将是最快的。连接系统表pg_attribute或信息模式中的列，并在DO语句中动态执行或编写函数以供重复使用：

    CREATE OR REPLACE FUNCTION f_row_copy(_tbl regclass, _id int, OUT row_ct int)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE (
      SELECT format('INSERT INTO %1$s(%2$s) SELECT %2$s FROM %1$s WHERE id = $1',
                    _tbl, string_agg(quote_ident(attname), ', '))
      FROM   pg_attribute
      WHERE  attrelid = _tbl
      AND    NOT attisdropped  -- no dropped (dead) columns
      AND    attnum > 0        -- no system columns
      AND    attname <> 'id'   -- exclude id column
      )
   USING _id;

   GET DIAGNOSTICS row_ct = ROW_COUNT;  -- directly assign OUT parameter
END
$func$;

    称呼：

    SELECT f_row_copy('people', 9);

    适用于任何具有名为 的整数列的表id。您也可以轻松地使列名动态化...

    也许不是您的首选，因为您想这样做stay away from stored procedures，但话又说回来，无论如何，它不是“存储过程” ...

    有关的：

    先进的解决方案

    列是一种serial特殊情况。如果您想用各自的默认值填充更多或所有列，它会变得更加复杂。考虑这个相关的答案：

    • 20

  2. 尝试创建一个trigger插入：

    CREATE TRIGGER name BEFORE INSERT

    在此触发器中，您将 ID 设为 NULL。触发器完成后，插入完成，Postgres 将提供一个 ID。我假设您已将 ID 定义为DEFAULT NEXTVAL('A_SEQUENCE'::REGCLASS).

    • 0

  3. 动态 SQL 工作得很好，几年来我一直在寻找这个，

    如果您有多个排除列，请简单地尝试，

    AND    attname <> 'id'   -- exclude id column
AND    attname <> 'second_col_name'   -- exclude second_col_name
    • -2

相关问题