如何创建自动生成多字段组合主键的触发器？解决重复键报错

我有一个地理空间数据库，包含locations表和features表：

• locations表主键为location_id，该字段同时是features表的外键
• features表包含type字段（两位代码表示要素类型）和N字段（区分同一位置的不同要素）

我认为location_id、type与N的组合可以作为features表的主键，之前手动输入这个主键，现在希望用户输入location_id、type和N时自动生成主键。（理想情况是自动生成正确的N，让用户只输入location_id和type，这个会单独提问）

尝试用触发器实现功能，但插入新数据时收到错误：duplicate key value violates unique constraint features_pkey，触发器代码如下：

CREATE OR REPLACE FUNCTION set_features_id()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
DECLARE
    compos_id text;
BEGIN
    SELECT loc_id ||  type  || N FROM features INTO compos_id;
    NEW.id := compos_id;
    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS set_lf_id_trigger on public.landscape_features_point;

CREATE TRIGGER set_features_id_trigger
    BEFORE INSERT
    ON "features"
    FOR EACH ROW
EXECUTE PROCEDURE set_features_id(); 

错误原因 #

1. 逻辑错误：触发器里的SELECT loc_id || type || N FROM features INTO compos_id完全错误——它会从features表的已有数据里随机选取一行的字段拼接成compos_id，然后赋值给新行的id，这必然会和已有行的主键重复，触发唯一约束冲突。
2. 字段名不匹配：问题描述里locations表的主键是location_id，但触发器代码里写的是loc_id，如果表实际字段名是location_id，这会直接导致查询报错。
3. 触发器表不一致：删除触发器的语句针对的是public.landscape_features_point表，但创建触发器时用的是features表，属于无效操作。

正确实现代码 #

直接用新插入行的location_id、type、N字段拼接生成主键即可，无需查询已有数据：

CREATE OR REPLACE FUNCTION set_features_id()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
BEGIN
    -- 注意：如果你的字段名是location_id，把下面的loc_id改成location_id
    NEW.id := NEW.loc_id || NEW.type || NEW.N;
    RETURN NEW;
END;
$$;

-- 统一触发器关联的表为features
DROP TRIGGER IF EXISTS set_features_id_trigger on public.features;

CREATE TRIGGER set_features_id_trigger
    BEFORE INSERT
    ON "features"
    FOR EACH ROW
EXECUTE PROCEDURE set_features_id(); 

额外注意事项 #

• 确保features表的id字段是文本类型（如varchar），能容纳拼接后的字符串长度。
• 如果location_id是数值类型，若需要固定长度避免歧义（比如数字补零），可以用LPAD(NEW.location_id::text, 5, '0')这类函数处理（示例为补零到5位，可按需调整）。

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