PostgreSQL中使用行类型插入含SERIAL主键列的数据问题

问题核心：当使用行类型变量插入包含SERIAL主键的表时，因变量中id字段为null，触发非空约束报错。以下是具体分析和解决方案：

问题根源 #

当查询不到记录时，行类型变量r_some_table_rt的id字段值为null。直接通过values (r_some_table_rt.*)插入整行时，PostgreSQL不会自动将null替换为SERIAL列的默认序列值——只有不指定该列或显式使用default关键字时，才会触发自增逻辑。

你尝试的方法失败原因 #

• 常规指定列插入成功：因为未传入id字段，PostgreSQL自动调用序列生成值；
• r_some_table.id := default报错：default是SQL语句关键字，无法直接赋值给PL/pgSQL变量；
• currval报错：当前会话未调用过该序列的nextval，currval只能获取当前会话中最近一次nextval生成的值。

可行解决方案 #

方案1：显式指定插入列（推荐） #

插入时仅指定业务字段，排除id，让PostgreSQL自动处理自增：

declare
    r_some_table_rt some_table%rowtype;
begin
    select *
    into r_some_table_rt
    from some_table
    where some_value = 'Some value'; -- 修正原代码中不存在的some_column字段

    if r_some_table_rt.id is null then
        r_some_table_rt.some_value := 'Some other value';

        -- 显式指定列，依赖SERIAL默认值生成id
        insert into some_table (some_value)
        values (r_some_table_rt.some_value)
        returning id into r_some_table_rt.id;
    end if;

    return r_some_table_rt.id;
end;

方案2：预生成id值后整行插入 #

若需保留整行插入的方式，可通过nextval提前获取序列值赋值给id字段：

declare
    r_some_table_rt some_table%rowtype;
begin
    select *
    into r_some_table_rt
    from some_table
    where some_value = 'Some value';

    if r_some_table_rt.id is null then
        r_some_table_rt.some_value := 'Some other value';
        -- 手动调用序列生成id
        r_some_table_rt.id := nextval(pg_get_serial_sequence('some_table', 'id'));

        insert into some_table
        values (r_some_table_rt.*)
        returning id into r_some_table_rt.id;
    end if;

    return r_some_table_rt.id;
end;

额外修正 #

原代码查询条件中的some_column为不存在字段，需改为表结构中的some_value，否则会导致查询始终无结果，触发插入逻辑。

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