PostgreSQL 15：INSERT...SELECT复制bigserial行时NULL未触发自增问题

问题根源 #

bigserial类型本质是bigint NOT NULL + 自动生成的序列 + 默认值nextval(序列名)。当你显式插入NULL到id列时，PostgreSQL不会触发默认值的序列自增逻辑，直接违反NOT NULL约束导致报错。

可行解决方案 #

方案一：动态生成非id列的插入语句（无需手动列所有字段） #

利用information_schema.columns系统视图自动获取除id外的所有列名，避免手动维护字段列表，适合字段较多的场景：

1. 先执行以下查询获取目标列名：

SELECT string_agg(column_name, ', ')
FROM information_schema.columns
WHERE table_name = 'src' AND column_name != 'id';

2. 用查询结果构造插入语句（假设返回结果是txt，实际会自动列出所有非id列）：

INSERT INTO src (txt)
SELECT txt FROM src WHERE txt = 'b';

3. 或者用动态SQL一键执行，无需手动复制列名：

DO $$
DECLARE
    cols text;
BEGIN
    SELECT string_agg(column_name, ', ')
    INTO cols
    FROM information_schema.columns
    WHERE table_name = 'src' AND column_name != 'id';
    
    EXECUTE format('INSERT INTO src (%s) SELECT %s FROM src WHERE txt = ''b''', cols, cols);
END $$;

方案二：修改临时表id列的默认值（兼容原临时表思路） #

如果一定要用临时表中转，可修改临时表的id列，让它复用原表的序列默认值，插入时自动生成新id：

CREATE temp TABLE src_temp AS SELECT * FROM src WHERE txt = 'b';
-- 给临时表id列设置原表的序列默认值（序列名格式为<表名>_<列名>_seq）
ALTER TABLE src_temp ALTER COLUMN id SET DEFAULT nextval('src_id_seq'::regclass);
-- 将临时表的id值重置为默认值，插入时会自动生成新id
UPDATE src_temp SET id = DEFAULT;
-- 执行插入
INSERT INTO src SELECT * FROM src_temp;

方案三：直接在SELECT中排除id列（简化写法） #

如果能接受用行记录拆分的写法，可直接排除id字段：

INSERT INTO src
SELECT (src).* FROM (SELECT id AS dummy, src.* FROM src WHERE txt = 'b') t;

这里通过子查询把id重命名为dummy，外层用(src).*获取除dummy外的所有原字段，相当于自动排除了id列。

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