Hrm, I didn't realize that. Is it in the docs anywhere? I didn't see it
in Chapter 11... I'm particularly interested in why NULL/NOT NULL isn't
indexable.
Are where clauses on indexes like
email_contrib__team_id btree (team_id) WHERE (team_id IS NOT NULL)
still valid/usefull? If I wanted to create the converse of that index,
could I do something like
CREATE INDEX email_contrib__no_team ON
email_contrib(COALESCE(team_id,true)) WHERE team_id IS NULL;
and
SELECT ... WHERE COALESCE(team_id, true) = true;
?
On Thu, Feb 05, 2004 at 03:23:16PM -0500, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > I build a table to test the theory that PGSQL wouldn't use an index to
> > satisfy 'SELECT * FROM table WHERE field IS NOT NULL'.
>
> IS NULL/IS NOT NULL are not indexable operators.
>
> regards, tom lane
>
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
