Skip to content
Advertisement

Postgres check constraint in text array for the validity of the values

I want to create something similar to this

CHECK (ALL(scopes) IN ('read', 'write', 'delete', 'update'))

scopes here is a field in the table which is text[] and I want to be sure that all the values in this array are one of the values above. Any opinions on this? And also is it possible the get these values via SELECT from another table?

I have seen the below solution but I was curious if there is a simpler one.

Postgresql check constraint on all individual elements in array using function

Advertisement

Answer

demo:db<>fiddle

Using the <@ operator:

CHECK(scopes <@ ARRAY['read', 'write', 'delete', 'update'])

Not knowing your exact use case, but I would prefer a more normalized solution: Putting the four operations into a separate table which can be updated. Then you can work with foreign keys instead of the check contraint. If you have to update these four keywords you do not need to change the table DDL but only the values in the foreign table.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement