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

Answer 1

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

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