PostgreSQL使用DO ALSO RULE做审计表时SERIAL列ID不一致如何解决

你遇到的父表与审计表SERIAL列值不一致的问题，核心是审计表的id列额外配置了和父表相同的序列作为默认值，插入审计表时如果没有显式覆盖该默认值，就会重复调用父表的序列，导致审计表id比父表对应id大1。

SERIAL是PostgreSQL的语法糖，本质是给int列绑定了一个序列默认值，父表插入时会调用一次序列生成id，若审计表id也绑定了同一个序列，插入时就会再次调用序列，最终出现你遇到的id不匹配的情况。

1. 修正审计表id列属性 #

审计表的id完全继承自父表，不需要自行生成，所以先删除审计表id列的默认值即可：

ALTER TABLE audit_testing_audit ALTER COLUMN id DROP DEFAULT;

如果是后续新创建审计表，更推荐使用LIKE语法复制父表结构，避免属性继承异常：

-- 先创建审计表，复制父表的所有列属性，再追加审计专用字段
CREATE TABLE audit_testing_audit (
    audit_type VARCHAR NOT NULL,
    audit_time TIMESTAMP NOT NULL,
    LIKE audit_testing_parent INCLUDING ALL
);
-- 删除id列的默认值，禁止审计表自行生成id
ALTER TABLE audit_testing_audit ALTER COLUMN id DROP DEFAULT;

2. 确认Rule逻辑 #

你原来的Rule写法已经符合要求，只要Rule中显式指定插入审计表的id值为new.id，就会直接使用父表生成好的id，不会触发审计表的默认值，参考写法如下：

CREATE OR REPLACE RULE audit_testing_parent_on_insert 
AS ON INSERT TO audit_testing_parent DO ALSO
INSERT INTO audit_testing_audit (
    audit_type,
    audit_time,
    id,
    varchar_col1,
    varchar_col2,
    int_col,
    created_on
) VALUES (
    'INSERT',
    now(),
    new.id,
    new.varchar_col1,
    new.varchar_col2,
    new.int_col,
    new.created_on
);

调整完成后再执行插入测试，两表的id值就会完全一致。

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