From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Rafal Pietrak <r(dot)pietrak(at)sm(dot)strop(dot)com(dot)pl> |
Cc: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: it's a feature, but it feels like a bug |
Date: | 2018-02-08 03:22:46 |
Message-ID: | CAKFQuwbn0cUJ93XcNDas5+z37F-W0LgLM1HU8KsJL+8AVSocwQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-02-08 03:26:50 | Re: it's a feature, but it feels like a bug |
Previous Message | Claudio Freire | 2018-02-08 03:13:09 | Re: [HACKERS] Vacuum: allow usage of more than 1GB of work mem |