Menu
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
232
The PostgreSQL provides the CHECK constraint, which allows the user to define a condition, that a value entered into a table, has to satisfy before it can be accepted. The CHECK constraint consists of the keyword CHECK followed by parenthesized conditions.
PostgreSQL makes it less complicated for using arrays in a query and finding the length of a column using only the simple syntax array_length (column_name, int). The “array_length” in this syntax returns the length of an array of the first argument i.e., column_name, and “int” tells the dimension of the array measured.
SELECT conname FROM pg_constraint WHERE conrelid = (SELECT oid FROM pg_class WHERE relname LIKE 'tableName'); Also you can get it from pgAdmin in objects tree.
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.
143
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
