2

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
    This is not what arrays should be used for. Create a proper 1-to-many relationship with a foreign key. – a_horse_with_no_name Oct 9 '19 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 '19 at 12:17
0

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

CHECK (countries <@ ARRAY['Morocco', 'Mali', 'Indonesia'])
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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