Re: Indexing null dates

Tom Lane said:
> "John Sidney-Woollett" <johnsw@wardbrook.com> writes:
>> [ needs to make this fast: ]
>>   -- locate the next order
>>   select WCCustOrderID into vCustOrderID
>>   from CUSTOMER.WCCustOrderStatusLog
>>   where WCOrderStatusID = pStatusID
>>   and Acknowledged is null
>>   and Processing is null
>>   for update
>>   limit 1;
>
>> My question is whether postgres can index null values, and if not, do I
>> have to accept a full table scan when locating records.
>
> It indexes them, but "is null" is not an indexable operator, so you
> can't directly solve the above with a 3-column index.  What you can do
> instead is use a partial index, for instance
>
> create index i on CUSTOMER.WCCustOrderStatusLog (WCOrderStatusID)
> where Acknowledged is null and Processing is null;

That's a very nifty trick and exactly the sort of answer I was after!

Many thanks

John Sidney-Woollett