Re: Multicolumn primary key with null value

On 23/04/10 15:50, Adrian von Bidder wrote:
> On Friday 23 April 2010 03.27:29 Craig Ringer wrote:
>> insert into test (a,b) values ('fred',NULL);
>> insert into test (a,b) values ('fred',NULL);
>>
>>
>> ... and will succeed:
>
> Hmm. Perhaps not as ugly as "none" placeholders:
>
> create unique index on test (b) where a is null;
> create unique index on test (a) where b is null;

True ... and Pg can even use them both together for bitmap index scans,
albeit not as efficiently as a single multicolumn index.

This really isn't viable for >2 nullable fields, though, as the number
of indexes increases to impractical levels rather quickly.

--
Craig Ringer