Re: [SQL] how to tell the difference between empty field and null field

From: "Moray McConnachie" <moray(dot)mcconnachie(at)computing-services(dot)oxford(dot)ac(dot)uk>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: [SQL] how to tell the difference between empty field and null field
Date: 1999-12-13 10:40:44
Message-ID: 005d01bf4556$82cb0370$760e01a3@oucs.ox.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Far be it from me to defend Microsoft, but not all sloppiness is down
to Microsoft. All Microsoft databases support IS NULL, and indeed
advocate it: from the Access help file...

Searching for Null values or zero-length strings
----------------------------------------------------------------
If you're using a query to search for Null values or zero-length
strings, type Is Null into the Criteria cell to search for Null
values, or type two double quotation marks (" ") into the Criteria
cell to search for zero-length strings (don't type a space between the
quotation marks).

Yours,
Moray
----------------------------------------------------------------------
----------------
Moray(dot)McConnachie(at)computing-services(dot)oxford(dot)ac(dot)uk
----- Original Message -----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alex Howansky <alex(at)wankwood(dot)com>
Cc: <pgsql-sql(at)postgreSQL(dot)org>
Sent: Monday, December 13, 1999 5:14 AM
Subject: Re: [SQL] how to tell the difference between empty field and
null field

> Alex Howansky <alex(at)wankwood(dot)com> writes:
> > select * from users where domain = '' or domain = null;
>
> OK, that'll work, but if you'll pardon a nitpick: "= NULL" is not
> standard, it is Microsoft brain damage. "IS NULL" is standard.
>
> > Here's my question: if I have a zillion records in this table, and
> > it's indexed by user+domain, how can I run this query without
losing
> > the benefit of the index?
>
> An index on (user, domain) is perfectly useless for the above query,
> because the user field isn't mentioned anywhere in the query. An
index
> on domain alone could be used, though, and should be pretty
effective.
> (We do have some performance problems if you get into dozens of OR
> terms, but for just a couple, no sweat.)
>
> As a rule of thumb, multi-column indexes are quite inflexible, and
> you will not find that they are worth their cost of upkeep unless
> you know that you have a specific kind of query you use a *lot* that
> can exploit the multi-column index. Not only that, but that the
> heavily used query is significantly faster than it'd be if it only
> had an index on the first column named in the multi-column index.
> In most scenarios, the first column gives you most of the gain and
> any extra columns are marginal.
>
> In short, unless you've done careful analysis and testing, you
should
> not make an index on (user, domain) but two indexes on user and
domain
> separately. The latter setup will be a lot more flexible in terms
of
> being reasonably quick for a variety of queries.
>
> regards, tom lane
>
> ************
>
>

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Steven M. Wheeler 1999-12-13 20:30:07 How do I get column names?
Previous Message Tom Lane 1999-12-13 08:00:31 Re: [SQL] Problem copying polygon data into a table