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
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.