如何在PostgreSQL现有表的新列中插入指定列表值？

先明确需求：
你有一组固定文本值：('apple', 'orange', 'banana')，现有一个两列表，要新增一列，把这三个值按顺序对应到每一行中，而非填充单一默认值、随机值或数字序列。

原表结构：

目标表结构：

下面是不同数据库的实现方案：

核心思路：给每行分配行号，关联文本序列更新 #

先给原表每行生成唯一行号，再将行号与指定文本值一一对应，最后完成新列的填充。

MySQL/MariaDB 操作步骤 #

1. 先新增目标列：

ALTER TABLE your_table ADD COLUMN `New Column` VARCHAR(50);

2. 用变量生成行号并关联更新：
   如果表有主键（比如id），用主键关联能保证行顺序准确：

SET @row_num = 0;

UPDATE your_table t1
JOIN (
    SELECT id, @row_num := @row_num + 1 AS rn
    FROM your_table
) t2 ON t1.id = t2.id
JOIN (
    SELECT 1 AS rn, 'apple' AS val UNION ALL
    SELECT 2 AS rn, 'orange' AS val UNION ALL
    SELECT 3 AS rn, 'banana' AS val
) seq ON t2.rn = seq.rn
SET t1.`New Column` = seq.val;

若没有主键，可临时生成行号（注意：行顺序可能受存储引擎影响，需确保文本值数量与表行数一致）：

SET @row_num = 0;

UPDATE your_table
JOIN (
    SELECT @row_num := @row_num + 1 AS rn, val
    FROM (SELECT 'apple' UNION ALL SELECT 'orange' UNION ALL SELECT 'banana') AS vals(val)
) seq ON your_table.rn = seq.rn;

PostgreSQL 操作步骤 #

1. 新增目标列：

ALTER TABLE your_table ADD COLUMN "New Column" VARCHAR(50);

2. 用窗口函数生成行号并关联更新：

WITH numbered_rows AS (
    SELECT *, ROW_NUMBER() OVER () AS rn
    FROM your_table
),
value_seq AS (
    SELECT UNNEST(ARRAY['apple', 'orange', 'banana']) AS val, GENERATE_SERIES(1,3) AS rn
)
UPDATE your_table t
SET "New Column" = vs.val
FROM numbered_rows nr
JOIN value_seq vs ON nr.rn = vs.rn
WHERE t.id = nr.id; -- 替换为你的表主键列

SQL Server 操作步骤 #

1. 新增目标列：

ALTER TABLE your_table ADD [New Column] VARCHAR(50);

2. 用CTE生成行号并关联更新：

WITH numbered_rows AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM your_table
),
value_seq AS (
    SELECT val, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM (VALUES ('apple'), ('orange'), ('banana')) AS vals(val)
)
UPDATE nr
SET [New Column] = vs.val
FROM numbered_rows nr
JOIN value_seq vs ON nr.rn = vs.rn;

备选方案：直接生成新表（允许重建表时使用） #

如果不需要修改原表，可直接创建包含新列的表：

-- MySQL示例
CREATE TABLE new_table AS
SELECT t.*, seq.val AS `New Column`
FROM (
    SELECT *, @row_num := @row_num + 1 AS rn
    FROM your_table, (SELECT @row_num := 0) AS init
) t
JOIN (
    SELECT 1 AS rn, 'apple' UNION ALL
    SELECT 2 AS rn, 'orange' UNION ALL
    SELECT 3 AS rn, 'banana'
) seq ON t.rn = seq.rn;

注意事项 #

• 文本值的数量必须与表的行数完全一致，否则会出现部分行无值或重复填充的情况。
• 优先使用主键关联行号，避免因表存储顺序变化导致值对应错误。

内容的提问来源于stack exchange，提问作者Hochy_Marmeladky