Re: it's a feature, but it feels like a bug

On Wednesday, February 7, 2018, Rafal Pietrak <r(dot)pietrak(at)sm(dot)strop(dot)com(dot)pl>
wrote:

> Hi,
>
> I've bumped onto the following problem:
> ---------------------screenshot----------------------------
> ztk=# create table test (a int, b int, c int, d bool, e int, primary key
> (a,b,c,d));
> CREATE TABLE
> ztk=# create unique index leftone on test (a,b) where d is true;
> a unique index is there to indicate exactly one row as target
> for the FK. This is what LEFTONE index does.
>

It is quite possible in your schema that (a,b) = (1,2) can appear three
times (or at least twice) in your table...once where d is true, once where
d is false, and once where d is null. The FK references the columns of the
table, not a named index. So any index that doesn't cover the whole table
cannot be used.

If you want to enforce something like this you can use triggers.

David J.