Check if value exists in postgres array for partitioning via check constraint

Question

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[])

Just remove the `select` then it should work in a check constraint. — a_horse_with_no_name, 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

David Schumann · Accepted Answer · 2015-07-29T09:31:59.497

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.

edited Jul 29 '15 at 09:31

answered Jul 29 '15 at 08:54

David Schumann

11,499
8
67
87

1

I don't see a reason why you need an array at all: Just use `check (language in ('en', 'pt', 'es', 'fr'))` – a_horse_with_no_name 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

Check if value exists in postgres array for partitioning via check constraint

1 Answers1