5

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

2
  • 2
    This is not what arrays should be used for. Create a proper 1-to-many relationship with a foreign key.
    – user1822
    Oct 9, 2019 at 12:10
  • @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.
    – AndreKR
    Oct 9, 2019 at 12:17

1 Answer 1

3

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.