Re: Indexing null dates

"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;

regards, tom lane