I would like to set up a CHECK constraint that makes sure that a text[]
column's elements contain only certain values.
Setting up an example:
CREATE TABLE foo(
countries text[]
);
INSERT INTO foo VALUES ('{"Morocco", "Mali", "Indonesia"}');
INSERT INTO foo VALUES ('{"Sokovia", "Mali"}');
Now only "Morocco", "Mali" and "Indonesia" should be allowed, so the second row should get rejected by the constraint.
I have a "working" solution:
CHECK (array_length(
array_remove(
array_remove(
array_remove(
countries,
'Indonesia'
), 'Mali'
), 'Morocco'
), 1) IS NULL)
But this is not very readable.
I tried this as well:
CHECK ((
SELECT unnest(countries)
EXCEPT
SELECT unnest(array['Morocco', 'Mali', 'Indonesia'])
) IS NULL)
but:
ERROR cannot use subquery in check constraint