CODEWITHSUNDEEP
javascriptnode.jspostgresqlknex.js
TwittorDrive
TwittorDrive

Reputation: 165

How to check if value exists in PosrgreSQL enum custom type?

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.

Upvotes: 1

Views: 1083

Answers (1)

user330315
user330315

Reputation:

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';

Upvotes: 2

Related Questions