Constrain array values to an allowed set of values

Question

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

• user1822: This is not what arrays should be used for. Create a proper 1-to-many relationship with a foreign key.
• AndreKR: @a_horse_with_no_name I could also use an array of ENUMs. I'm interested if there is a nice way to do it with text[] and CHECK, though, because it's kind of a useful skill.

Answer 1

Use the “is contained in” operator in your check constraint:

CHECK (countries <@ ARRAY['Morocco', 'Mali', 'Indonesia'])