Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create type us_state as enum ('AL', 'WA');
create table users(name text, state us_state)
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...
                                                       ^


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