PostgreSQL中插入时条件生成IDENTITY字段的实现方案求助

要实现插入table1时仅当未提供other_id值（或提供NULL）时自动生成该字段值的需求，不能直接用双IDENTITY列的写法，以下是两种可行方案：

方案一：序列+列默认值（最简单高效） #

这种方式利用PostgreSQL序列和列默认值，直接满足需求：

-- 清理原有表（如果存在）
DROP TABLE IF EXISTS table1;
-- 创建用于生成other_id的序列
CREATE SEQUENCE table1_other_id_seq;
-- 创建表
CREATE TABLE table1 (
    pk_id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    other_id INT DEFAULT nextval('table1_other_id_seq'),
    -- 若需要other_id唯一，添加唯一约束
    UNIQUE (other_id)
);
-- 可选：将序列与列关联，删除表时自动清理序列
ALTER SEQUENCE table1_other_id_seq OWNED BY table1.other_id;

测试插入 #

-- 不指定other_id，自动生成值
INSERT INTO table1 DEFAULT VALUES;
-- 显式指定other_id为NULL，自动生成值
INSERT INTO table1 (pk_id, other_id) VALUES (DEFAULT, NULL);
-- 指定other_id为具体值，使用提供的数值
INSERT INTO table1 (pk_id, other_id) VALUES (DEFAULT, 100);

方案二：触发器函数（适合复杂生成逻辑） #

如果需要更复杂的生成规则（比如非序列的自定义逻辑），可以用触发器实现：

DROP TABLE IF EXISTS table1;
CREATE SEQUENCE table1_other_id_seq;

CREATE TABLE table1 (
    pk_id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    other_id INT,
    UNIQUE (other_id)
);

-- 创建触发器函数：当other_id为NULL时自动赋值序列值
CREATE OR REPLACE FUNCTION set_other_id_if_null()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.other_id IS NULL THEN
        NEW.other_id := nextval('table1_other_id_seq');
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到表的插入事件
CREATE TRIGGER trigger_table1_set_other_id
BEFORE INSERT ON table1
FOR EACH ROW EXECUTE FUNCTION set_other_id_if_null();

原方案问题说明 #

1. 双IDENTITY列的错误：GENERATED ALWAYS AS IDENTITY会强制系统生成值，禁止手动赋值，即使你想提供other_id的具体值也会触发错误，完全不符合需求；
2. PL/pgSQL函数写法错误：原函数中return query other_id generated always as identity;是非法语法，RETURN QUERY后必须跟合法的SELECT语句，不能直接写IDENTITY的定义逻辑。

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