Change insert value if value is not in ENUM

user_5528589 0
Viewed 456

I want to insert value to Postgres

If I have enum type in a table and I want to change the input value is not in the list of enum

like

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'

how can I use conditional insert or update the value while inserting?

sql-insert jdbc postgresql sql java
1 Answers

As far as concerned, on conflict does not recognize enum violations. The INSERT documentation says:

The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error.

Unfortunately an enum type does not fall in that category (same applies to check constraints, which otherwise could have been a possible solution here).

We could resort the trick of listing the enum values with enum_range(), then using that in a conditional expression:

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)

Note that this produces a NULL value when the given value does not exists in the enum, rather than an arbitratry string such as 'NULL'; this looks to me like the proper way to represent unmatched values.

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;

name | state
:--- | :----
Bob  | null 
Bill | AL
user_106767160
Related