CODEWITHSUNDEEP
postgresqlenumspartitioningpostgresql-10
Damir Ciganović-Janković
Damir Ciganović-Janković

Reputation: 981

PostgreSQL 10 partition by list using enums

I am trying to partition my table using enum column and I am encountering a somewhat strange behaviour. 

create type positivity as enum (
    'POSITIVE',
    'NEGATIVE'
);

create table test (id int, polarity positivity) partition by list (polarity);
create table test_1 partition of test for values in ('POSITIVE');
create table test_2 partition of test for values in ('NEGATIVE');

explain select * from test where polarity = 'NEGATIVE';

For this code this is what I get for output of explain: enter image description here

My question is why is it working like that and is this a bug or a feature in postgres?

EDIT: Adding constraints manually improves the query plan and that way I've got what I wanted (but I am still wandering about the behaviour explained above):

alter table test_1 add constraint test_1_check check(polarity='POSITIVE');
alter table test_2 add constraint test_1_check check(polarity='NEGATIVE');

New query plan for the same explain command:

enter image description here

Upvotes: 1

Views: 2352

Answers (1)

evan_b
evan_b

Reputation: 1249

For anyone looking at this in mid 2018, the fix hasn't made it into release yet; I'm still seeing it happen on 10.4.

Here's the commit for the fix, which appears it is being reviewed this month - https://commitfest.postgresql.org/17/1591/

Upvotes: 1

Related Questions