At my PostgreSQL database I have custom enum type called vegetables_type. It contains ('carrot', 'potato', 'tomato') values. My task is to add new value to this type. I am looking for an SQL statement to check if this new value already exists in this type and if not - add this value, if it exists - do nothing.
I've find SELECT unnest(enum_range(NULL::vegetables_type)); statement. But I can't find how to operate on this result - it is not possible to apply WHERE, IN, ANY operations.
Move the call to unnest() to the FROM clause, then you can use the output like any other table:
SELECT *
FROM unnest(enum_range(NULL::vegetables_type)) as t(name)
WHERE name::text = 'carrot'
To test if a value exist, you can use e.g.:
select exists (select *
FROM unnest(enum_range(NULL::vegetables_type)) as t(name)
WHERE t.name::text = 'new_item')
The above will return true if new_item is already a member of the enum.
But you don't need such a complicated statement to begin with. You can simply use if not exists as part of the ALTER statement:
alter type vegetables_type add value if not exists 'new_item';