PostgreSQL建表定义中能否使用序列？多列同步场景报错求助

问题背景 #

要编写可在C#代码中执行的PostgreSQL脚本，满足以下要求：

• 创建包含Id和Id2列的表，新增记录时两列值必须一致（该设计不可更改）
• 使用序列生成Id的值
• 仅在C#代码中执行脚本，不使用pgAdmin等管理工具

执行原有脚本时出现错误：ERROR: syntax error at or near 'nextval'，原错误脚本如下：

CREATE SEQUENCE IF NOT EXISTS myTable_seq
START 1
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
CACHE 1;

CREATE TABLE IF NOT EXISTS myTable
(
     id bigint NOT NULL nextval('myTable_seq'),
     symbol bigint NOT NULL currval('myTable_seq'),
     CONSTRAINT PK_myTable_Id PRIMARY KEY (Id)
)

报错原因 #

1. PostgreSQL设置列默认值必须使用DEFAULT关键字，直接将nextval()写在列类型后的语法不被数据库识别
2. 原脚本列名错误：要求的Id2被写成了symbol；同时currval('myTable_seq')不能直接作为列默认值——currval需要当前会话先通过nextval生成过序列值才能调用，否则会触发额外错误

修正后的脚本 #

方案一：默认值关联（简单高效） #

通过将Id2的默认值设为Id，确保新增记录时两列值自动一致，Id使用序列生成默认值：

CREATE SEQUENCE IF NOT EXISTS myTable_seq
START 1
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
CACHE 1;

CREATE TABLE IF NOT EXISTS myTable
(
     id bigint NOT NULL DEFAULT nextval('myTable_seq'),
     id2 bigint NOT NULL DEFAULT id,
     CONSTRAINT PK_myTable_Id PRIMARY KEY (Id)
);

方案二：触发器强制同步（严谨防篡改） #

如果需要防止后续手动修改导致两列值不一致，可添加触发器，强制新增或更新时同步两列值：

CREATE SEQUENCE IF NOT EXISTS myTable_seq
START 1
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
CACHE 1;

CREATE TABLE IF NOT EXISTS myTable
(
     id bigint NOT NULL DEFAULT nextval('myTable_seq'),
     id2 bigint NOT NULL,
     CONSTRAINT PK_myTable_Id PRIMARY KEY (Id)
);

-- 创建触发器函数
CREATE OR REPLACE FUNCTION sync_id_id2()
RETURNS TRIGGER AS $$
BEGIN
    NEW.id2 := NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到表
CREATE TRIGGER trigger_sync_id_id2
BEFORE INSERT OR UPDATE ON myTable
FOR EACH ROW EXECUTE FUNCTION sync_id_id2();

补充说明 #

修正后的脚本均可直接通过Npgsql等PostgreSQL驱动在C#中执行，无需额外配置。仅需新增时保持一致选方案一，需防止后续篡改选方案二。

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