我想向Postgres插入值

如果我在表中有枚举类型并且想要更改输入值,则不在列表中
枚举

喜欢

create type us_state as enum ('AL', ..., 'NULL');

insert into user (name, state)
values ('Bob', 'AA')
conflict on state is not valid us_state update value = 'NULL'

如何在插入时使用条件插入或更新值?

最佳答案

就所关注的而言,on conflict无法识别枚举违例。 INSERT documentation说:

可选的ON CONFLICT子句指定引发唯一冲突或排除约束冲突错误的替代操作。

不幸的是,枚举类型不属于该类别(同样适用于检查约束,否则在这里可能是可能的解决方案)。

我们可以使用技巧,用enum_range()列出枚举值,然后在条件表达式中使用它:

insert into users(name, state)
select
    name,
    case when state = any(enum_range(null::us_state)::name[])
        then state::us_state
    end
from (values ('Bob', 'AA')) t(name, state)

请注意,当给定值不存在于枚举中时,它将生成NULL值,而不是诸如'NULL'之类的仲裁字符串;在我看来,这是代表无与伦比的价值的正确方法。

Demo on DB Fiddle :
create type us_state as enum ('AL', 'WA');
create table users(name text, state us_state);

-- an attempt with "on conflict"
insert into users (name, state) values ('Bob', 'AA') on conflict(state) do nothing
-- ERROR:  invalid input value for enum us_state: "AA";
-- LINE 1: insert into users (name, state) values ('Bob', 'AA') on conf...

-- new query
insert into users(name, state)
select
    name,
    case when state = any(enum_range(null::us_state)::name[])
        then state::us_state
    end
from (values ('Bob', 'AA'), ('Bill', 'AL')) t(name, state);
-- 2 rows affected

select * from users;

名称|州
:- :----
鲍勃|空值
帐单|铝

08-05 03:48
查看更多