7

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

leventunver
  • 3,269
  • 7
  • 24
  • 39

1 Answers1

10

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.

S-Man
  • 22,521
  • 7
  • 40
  • 63
  • 1
    Great answer and advise! – johey Jul 19 '19 at 11:47
  • 1
    Thanks a lot for the quick answer. Worked already. I am also considering that option but good to know that I have this alternative in hand that I can always rely upon. Detailed discussion here: https://stackoverflow.com/questions/10923213/postgres-enum-data-type-or-check-constraint – leventunver Jul 19 '19 at 11:50