PostgreSQL插入数据时枚举字段传NULL如何自动使用默认值

PostgreSQL 列级 DEFAULT 配置仅在两种场景下生效：

• INSERT 语句的字段列表未包含该列
• 插入时该列位置显式书写 DEFAULT 关键字
  如果显式为字段传入 NULL，数据库会判定为主动写入空值，不会触发默认值填充逻辑，该行为对所有数据类型生效，和字段是否为枚举类型无关。

方案1：调整预编译SQL，通过COALESCE自动替换空值（推荐） #

无需修改表结构、无需额外维护触发器，仅调整插入SQL即可适配入参混有合法枚举值、NULL的场景，不需要在业务代码中提前判断参数是否为空。
如果默认值变动频率低，可以直接在SQL中硬编码默认值，性能最优：

-- 以你的CANDIDATE表为例
INSERT INTO candidate(candidate_status, interview_rating, first_name)
VALUES (
  COALESCE(?, 'not attended'::enum_candidate_status),
  ?,
  ?
)

如果希望默认值和表结构定义自动同步，避免后续改表默认值时漏改SQL，可以用以下写法自动读取列配置的默认值：

INSERT INTO candidate(candidate_status, interview_rating, first_name)
VALUES (
  COALESCE(?, (SELECT column_default::enum_candidate_status FROM information_schema.columns WHERE table_name = 'candidate' AND column_name = 'candidate_status')),
  ?,
  ?
)

逻辑说明：当占位符?传入非空枚举值时，直接使用传入值写入；当传入NULL时，自动使用预设默认值填充，完全匹配业务场景。

方案2：配置BEFORE INSERT行级触发器统一处理 #

如果表的插入入口较多，不想逐个修改插入SQL，可以创建前置触发器，插入前自动将NULL字段替换为默认值。
首先创建触发器处理函数：

CREATE OR REPLACE FUNCTION fill_candidate_default_enum()
RETURNS TRIGGER AS $$
BEGIN
  -- 字段为NULL时自动填充默认值
  IF NEW.candidate_status IS NULL THEN
    NEW.candidate_status := 'not attended'::enum_candidate_status;
  END IF;
  -- 其他需要自动填充默认的枚举字段可按相同逻辑追加判断
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

将触发器绑定到目标表：

CREATE TRIGGER trg_candidate_enum_default
BEFORE INSERT ON candidate
FOR EACH ROW
EXECUTE FUNCTION fill_candidate_default_enum();

该方案对所有插入该表的操作生效，缺点是调整默认值时需要同步修改触发器函数逻辑。

补充优化：添加NOT NULL约束避免脏数据 #

如果业务规则不允许枚举字段存储NULL，可以给字段追加非空约束，从根源避免异常空值写入，可配合上述两个方案使用：

ALTER TABLE candidate ALTER COLUMN candidate_status SET NOT NULL;

你提供的建表语句末尾存在多余逗号，执行会报错，修正后版本如下：

CREATE TYPE enum_candidate_status AS ENUM('attended', 'selected', 'rejected', 'not attended');   

CREATE TYPE enum_rating AS ENUM('1', '2', '3', '4', '5');  
 
CREATE TABLE CANDIDATE (
  candidate_id SERIAL PRIMARY KEY, 
  first_name varchar(100), 
  candidate_status enum_candidate_status DEFAULT 'not attended',
  interview_rating enum_rating
);

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