PostgreSQL 添加新的非空列，并使用插入语句中的 ID 填充

7 浏览
0 Comments

PostgreSQL 添加新的非空列，并使用插入语句中的 ID 填充

我有两个表。\n

CREATE TABLE content (
  id bigserial NOT NULL,
  name text
);
CREATE TABLE data (
    id bigserial NOT NULL,
    ...
);

\n这两个表已经有很多数据了。\n现在我想向data表中添加一个新列content_id（不为空）。\n它应该是一个对content表的外键。\n是否可以自动在content表中创建一个条目，以在data表中设置content_id。\n例如\n**content**\n| id | name |\n| 1 | abc | \n| 2 | cde |\n\ndata\n| id |... |\n| 1 |... |\n| 2 |... |\n| 3 |... |\n\n现在我需要一个更新语句，创建3个（在此示例中）content条目，并将id添加到data表中，以获得以下结果：\ncontent\n| id | name |\n| 1 | abc |\n| 2 | cde |\n| 3 | ... |\n| 4 | ... |\n| 5 | ... |\n\ndata\n| id |... | content_id |\n| 1 |... | 3 |\n| 2 |... | 4 |\n| 3 |... | 5 |\n

0

1 答案

0 Comments

问题的出现原因是在PostgreSQL数据库中添加一个新的NOT NULL列，并且需要用插入语句中的id值进行填充。解决方法如下：

步骤1：添加新列（没有NOT NULL约束，因为此时新列的值不可用）

ALTER TABLE data ADD COLUMN content_id integer;

步骤2：在一行中将数据插入两个表中：

WITH inserted AS (
    INSERT INTO content
    SELECT
        generate_series(
            (SELECT MAX(id) + 1 FROM content),
            (SELECT MAX(id) FROM content) + (SELECT COUNT(*) FROM data)
        ),
        'dummy text'
    RETURNING id
), matched AS (
   SELECT
       d.id AS data_id,
       i.id AS content_id
   FROM (
       SELECT
           id,
           row_number() OVER ()
       FROM data
   ) d 
   JOIN (
      SELECT
          id,
          row_number() OVER ()
      FROM inserted
   ) i ON i.row_number = d.row_number
)
UPDATE data d
SET content_id = s.content_id
FROM (
    SELECT * FROM matched
) s
WHERE d.id = s.data_id;

通过使用WITH子句（CTEs），可以执行多个语句，每个语句都使用前一个语句的结果。

1. 将数据插入content表：这会生成一个整数序列，从当前content表的id值的MAX() + 1开始，记录数与data表相同。然后返回新的id值。

2. 现在我们需要将data表的当前记录与新id进行匹配。因此，对于两边，我们使用row_number()窗口函数为每个记录生成连续的行计数。因为插入结果和实际的data表具有相同数量的记录，所以可以将其用作连接条件。因此，我们可以将data表的id列与新的content表的id值进行匹配。

3. 这些匹配的数据可以在新的content_id列的最终更新中使用。

步骤3：添加NOT NULL约束

ALTER TABLE data ALTER COLUMN content_id SET NOT NULL;

0