how to tell the difference between empty field and null field

Поиск
Список
Период
Сортировка
От Alex Howansky
Тема how to tell the difference between empty field and null field
Дата
Msg-id Pine.LNX.4.20.9912122157220.18303-100000@net-srv-0001.bvrd.com
обсуждение исходный текст
Ответы Re: [SQL] how to tell the difference between empty field and null field  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-sql
Assuming a table such as this:

create table users ( user text, password text, name text, domain text
);

...and data such as this:

insert into users values ('frank','zCeZ6f2f.NUKU','Frank Farley','domain.com');

insert into users values ('joe','QJixz/XLXvio2','Joe Blogg','');

insert into users values ('sam','kAdhVr3URa4Y.','Sam Stooge');

Note that joe has a blank domain field, while sam has none.

I want to know what users don't have a domain specified in their domain field.
But the query:

select * from users where domain = '';

only shows me joe, and the query:

select * from users where domain = null;

only shows me sam.

So, I use:

select * from users where domain = '' or domain = null;

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?

--
Alex Howansky
alex@wankwood.com
http://www.wankwood.com/



В списке pgsql-sql по дате отправления:

Предыдущее
От: "Bruno Mendonca"
Дата:
Сообщение:
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [SQL] how to tell the difference between empty field and null field