1

I found this very similar question but all of the answer started with a 'select' statement. I want to check whether a string is contained in a constant array with about 30 other strings. I could write a long x == a OR x == b OR... statement but I thought there might be a cleaner way.

So this doesn't work as a constraint check: SELECT language = ANY ('{"en", "pt", "es", "fr"}'::text[])

Community
  • 1
  • 1
David Schumann
  • 13,380
  • 9
  • 75
  • 96
  • 1
    Just remove the `select` then it should work in a check constraint. –  Jul 29 '15 at 08:41
  • This works, thanks for that. But this ANY function seems to be too complex for postgres to automatically ignore for selects on partitioned tables. – David Schumann Jul 29 '15 at 09:10

1 Answers1

1

Just removing the SELECT works:

CHECK(
    language = ANY ('{"en", "pt", "es", "fr"}'::text[])
)

But as a_horse_with_no_name pointed out:

Not using an array is even better, as this does not break the partitioning optimization.

CHECK(
    not( language in ('en', 'pt', 'es'))
)

Now SELECT * FROM myTable WHERE language='de'; will not even look at this table.

David Schumann
  • 13,380
  • 9
  • 75
  • 96
  • 1
    I don't see a reason why you need an array at all: Just use `check (language in ('en', 'pt', 'es', 'fr'))` –  Jul 29 '15 at 09:19
  • That is indeed much better. The Array actually broke the partitioning optimization. – David Schumann Jul 29 '15 at 09:30