PostgreSQL如何禁止INSERT语句为带默认值的UID列指定自定义值

PostgreSQL中可以通过两种方案实现禁止手动传入UID列的需求，优先推荐权限控制方案：

方案1：列级权限控制（最优） #

直接通过数据库原生的权限系统，回收所有角色对UID列的写入权限，从根源上禁止用户在INSERT/UPDATE时指定该列的值，无需修改表结构或编写触发器。

1. 保留原始表结构即可：

CREATE TABLE public."RiskHeaders"
(
    "UID" uuid NOT NULL DEFAULT uuid_generate_v4(),
    "Name" character varying(35) NOT NULL
);

2. 执行权限回收语句：

-- 回收所有角色的UID列写入权限
REVOKE INSERT("UID"), UPDATE("UID") ON public."RiskHeaders" FROM PUBLIC;
-- 若需要禁止表所有者也手动写入，额外执行以下语句，替换为实际的所有者角色名
-- REVOKE INSERT("UID"), UPDATE("UID") ON public."RiskHeaders" FROM <table_owner_role>;

效果说明： #

当用户执行带UID列的INSERT语句时，会直接触发权限报错：ERROR: permission denied for table RiskHeaders，完全符合需求。该方案优势：

• 无额外业务逻辑，性能零损耗
• 表结构直观体现UID自动生成的特性
• 权限可灵活调整，无需修改表结构或代码

方案2：BEFORE INSERT触发器（兼容特殊场景） #

如果业务场景不适合使用权限控制，可以使用触发器方案，优化后可解决你原有方案的两个缺陷：

1. 定义表时设置一个业务上完全不可能出现的UUID作为哨兵默认值，同时加字段注释说明：

CREATE TABLE public."RiskHeaders"
(
    "UID" uuid NOT NULL DEFAULT 'a1a1a1a1-1111-2222-3333-abcdef123456'::uuid,
    "Name" character varying(35) NOT NULL
);
COMMENT ON COLUMN public."RiskHeaders"."UID" IS '系统自动生成的唯一标识，禁止手动赋值，默认值为内部识别哨兵';

2. 创建触发器函数与触发器：

CREATE OR REPLACE FUNCTION trg_check_uid_insert()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW."UID" = 'a1a1a1a1-1111-2222-3333-abcdef123456'::uuid THEN
        -- 匹配哨兵值说明用户未传UID，替换为随机UUID
        NEW."UID" := uuid_generate_v4();
    ELSE
        -- 用户主动传值，直接抛出异常
        RAISE EXCEPTION '禁止手动指定UID列的值，该列由系统自动生成';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_riskheaders_before_insert
BEFORE INSERT ON public."RiskHeaders"
FOR EACH ROW EXECUTE FUNCTION trg_check_uid_insert();

效果说明： #

• 字段注释明确说明UID的自动生成特性，表结构语义清晰
• 除了内部唯一的哨兵值外，任何用户手动传入的值都会直接报错，不存在静默替换的问题，用户猜中哨兵值的概率可忽略不计

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