Re: Indexing null dates - Mailing list pgsql-general

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

            regards, tom lane