Re: Index not used with IS NULL

so NULLs **DON'T** count in a unique index? You can have more than one
NULL in a single column UNIQUE constraint? I guess I am showing my
ignorance, I thought you could only have one.

I was planning to do some interesting default configuration for a column
value to ensure uniqueness, but flag an unknown value.

Tom Lane wrote:
>
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> >> And is_indexable_operator() will know that this is safe how? Or do you
> >> plan to fix the other three index types to support NULLs too?
>
> > I would have thought that the other index type supported null anyway, for
> > the purposes of uniqueness checks.
>
> Well, (a) the other index types don't support uniqueness checks, and (b)
> it wouldn't be relevant anyway, because multiple nulls don't violate
> a unique constraint. GIST does support nulls in second and subsequent
> columns of a multi-column index, because it *has* to do so, but not in
> the first column --- and hash and rtree don't store nulls at all.
>
> > I remember looking into this a while ago. My solution to that problem was
> > that x =3D NULL is always NULL and so doesn't need to go through the scan
> > anyway (index or sequential). Once you've taken care of the x =3D NULL case
> > elsewhere, you can use the available state for x IS NULL.
>
> But how do you get from point A to point B? You need to represent both
> cases in ScanKeys further upstream than where that conclusion can be
> drawn (namely _bt_orderkeys()) --- or else do some very substantial
> restructuring work, which is exactly the point.
>
> Also, this would amount to hard-wiring the assumption that indexable
> operators are always strict. Which is rather a curious assumption
> to be putting in, if your goal is to support the obviously-not-strict
> construct IS NULL as an indexable operator. (Now I believe we make
> that assumption anyway in the index access methods ... but wiring it
> into ScanKeys, which is a very widespread data structure, would be the
> death knell for any hope of removing it someday.)
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

--

Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
Sincerely, Dennis Gearon