Re: Indexing null dates

Tom Lane said:
> "John Sidney-Woollett" <johnsw(at)wardbrook(dot)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