Re: Does anybody use ORDER BY x USING y?

On E, 2005-09-19 at 11:24 +0200, Martijn van Oosterhout wrote:
> On Mon, Sep 19, 2005 at 11:13:05AM +0300, Hannu Krosing wrote:
> > > (1) IS NULL is not an indexable operation, so no, not without
> > > significant overhaul of the index AM API.
> >
> > But we do store NULLs in indexes, so why is it not indexable?
> >
> > This is either an interface bug (not making use of stored info) or
> > storage bug (wasting space storing unneccessary info)
>
> Err, indexes used to not store NULLs to save space. However, it turns
> out that SQL UNIQUE has something to say about NULLs in unique columns
> so they had to be included.

surely not UNIQUE

hannu=# create table tabuniq(i int );
CREATE TABLE
hannu=# create index tabuniq_ndx on tabuniq(i);
CREATE INDEX
hannu=# insert into tabuniq values(1);
INSERT 20560497 1
hannu=# insert into tabuniq values(2);
INSERT 20560498 1
hannu=# insert into tabuniq values(null);
INSERT 20560499 1
hannu=# insert into tabuniq values(null);
INSERT 20560500 1

maybe the problem is with PRIMARY KEY

> However, the machinary to decide if an index is usable assumes that
> usable operators have two arguments and IS NULL isn't really an
> operator in the PostgreSQL sense and doesn't have two arguments either.
>
> *If* that can be fixed, then we can be more flexible. But if it were
> easy it would have been done long ago...

sure :)

--
Hannu Krosing <hannu(at)skype(dot)net>