Postgresql EXCLUDE constraint with a predicate : unexpected result

Question

I was trying to take Postgresql's EXCLUDE example a little further, by adding a PREDICATE.

In one case it works like I expect it. In another case...not so

First example, with a predicate on "cage". Works just fine :

=> CREATE TABLE zoo(
    cage     INTEGER,
    animal   TEXT,
    EXCLUDE USING GIST (cage WITH =, animal WITH <>) WHERE (cage = 1)
);
CREATE TABLE

=> INSERT INTO zoo VALUES (1, 'zebra');
INSERT 0 1

=> INSERT INTO zoo VALUES (2, 'lion');
INSERT 0 1

=> INSERT INTO zoo VALUES (1, 'lion');
ERROR:  conflicting key value violates exclusion constraint "zoo_cage_animal_excl"
DETAIL:  Key (cage, animal)=(1, lion) conflicts with existing key (cage, animal)=(1, zebra).

So far, so good. (1, lion) conflicts indeed with (1, zebra).

Second example, with a predicate on "animal". Unexpected result :

=> CREATE TABLE zoo(
        cage     INTEGER,
        animal   TEXT,
        EXCLUDE USING GIST (cage WITH =, animal WITH <>) WHERE (animal = 'lion')
    );
    CREATE TABLE

    => INSERT INTO zoo VALUES (1, 'zebra');
    INSERT 0 1

    => INSERT INTO zoo VALUES (2, 'lion');
    INSERT 0 1

    => INSERT INTO zoo VALUES (2, 'zebra');
    INSERT 0 1

Why doesn't this last statement raise a conflicting error ?

WHERE (animal = 'lion') builds a partial GIST index. The last row ((2, zebra)) should conflict with the existing row (2, lion) (same cage, different animal : 2 = 2, zebra <> lion).

So how come that PostgreSQL allows this row (2, zebra) to be inserted ?

Mike Organek · Accepted Answer · 2020-07-21 15:58:50Z

2

Since 'zebra' <> 'lion', the zebra record is not included in the partial index.

In fact only lion records are included in the partial index.

It will never be true that 'lion' <> 'lion', so the exclude condition will never be met.

answered Jul 21, 2020 at 15:58

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

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

2 Comments

ChennyStar Over a year ago

OK, I think I understand. I initially thought that the only use of the WHERE predicate is to create the partial index on lion records. So, I thought that the index would hold indeed the (2, lion) row. And that (2, lion) row would prevent the insertion of the (2, zebra) row. If I understand what you say, the predicate filters each new row, before the EXCLUDE constraint is applied. So, in my case, at the last statement, the insertion of (2, zebra), it first checks if the index is supposed to deal with that row. If no it carries on and allows the insertion. Am I understanding it right ?

Mike Organek Over a year ago

@ChennyStar Yes. The (2, zebra) record never is put into the index because the index accepts only lion records. Because the index never gets the (2, zebra) record, it cannot cause the exclude to prevent the record from being inserted. You should also check your first example by putting both a lion and a zebra in a cage other than 1. The exclude will allow it since the index cares only about records where cage = 1.

Collectives™ on Stack Overflow

Postgresql EXCLUDE constraint with a predicate : unexpected result

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related