如何检查PostgreSQL自定义枚举类型中是否存在指定值并实现不存在则新增的操作？

嘿，我明白你的需求——想给vegetables_type枚举安全加新值，避免重复添加的报错对吧？你提到的SELECT unnest(enum_range(NULL::vegetables_type));其实是可以配合过滤条件的，但更高效可靠的方式是直接查询PostgreSQL的系统目录表，再结合PL/pgSQL的条件判断来实现你的需求。

方法一：用匿名DO块实现条件添加（推荐） #

PostgreSQL的DDL语句没法直接在普通SQL的IF里执行，但我们可以用匿名PL/pgSQL块（也就是DO语句）来完成“检查存在性→不存在则添加”的逻辑，这是一条可以直接执行的语句：

DO $$
BEGIN
    -- 检查新值是否已存在于枚举类型中
    IF NOT EXISTS (
        SELECT 1 
        FROM pg_enum 
        WHERE enumtypid = 'vegetables_type'::regtype  -- 指定要操作的枚举类型
          AND enumlabel = 'spinach'                   -- 替换成你要添加的新值
    ) THEN
        -- 不存在则执行添加操作
        ALTER TYPE vegetables_type ADD VALUE 'spinach';
    END IF;
END $$;

为什么不用你提到的unnest(enum_range)？ #

其实你那条语句是可以配合过滤条件的，只是需要把结果转成可比较的枚举类型格式，比如：

SELECT val 
FROM unnest(enum_range(NULL::vegetables_type)) AS vals(val)
WHERE val = 'spinach'::vegetables_type;

不过直接查pg_enum系统表会更高效，因为它直接存储了枚举类型的所有标签和对应的类型ID，不需要额外的拆分数组操作。

注意事项 #

• 枚举类型的值一旦添加就无法直接删除（除非删除整个枚举类型重建，这会影响依赖该类型的表和对象），所以添加前一定要确认值的正确性。
• 如果有并发操作的场景，这个DO块是原子执行的，不会出现竞态问题（比如两个请求同时添加同一个值，只会有一个执行成功，另一个无操作）。

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