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. — user330315
– user330315, Commented Jul 29, 2015 at 8: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
– David Schumann, Commented Jul 29, 2015 at 9:10

David Schumann · Accepted Answer · 2015-07-29 09:31:59Z

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, 2015 at 9:31

answered Jul 29, 2015 at 8:54

David Schumann

15k13 gold badges83 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

I don't see a reason why you need an array at all: Just use check (language in ('en', 'pt', 'es', 'fr'))

David Schumann Over a year ago

That is indeed much better. The Array actually broke the partitioning optimization.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related