Postgres Pro
Downloads
Home   >   mailing lists

Thread: NOT NULL Issue

NOT NULL Issue

From
"Gustav Lindenberg"
Date:
Hi

Why is ''  not considered null is postgres (8.1.3) Currently I have to
use the following workaround where I have zero length strings in char
fields.

select * from  security.users  where length(us_username)=0;
Surely this a null. Apparently not in Postgres.

Currently I have to use the following SQL to pick up zero length strings:
alter table security.users add constraint notnull_username
check(us_username <> '');

Thanks
Gustav

Re: NOT NULL Issue

From
Gregory Stark
Date:
"Gustav Lindenberg" <gustav.lindenberg@gmail.com> writes:

> select * from  security.users  where length(us_username)=0;
> Surely this a null.

Surely not.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

Re: NOT NULL Issue

From
Tom Lane
Date:
"Gustav Lindenberg" <gustav.lindenberg@gmail.com> writes:
> Why is ''  not considered null is postgres (8.1.3)

Because they're different.  The SQL spec says that an empty string
is different from NULL, and so does every database in the world except
Oracle.  Oracle, however, does not define the standard.

            regards, tom lane

Re: NOT NULL Issue

From
Geoffrey Myers
Date:
Tom Lane wrote:
> "Gustav Lindenberg" <gustav.lindenberg@gmail.com> writes:
>> Why is ''  not considered null is postgres (8.1.3)
>
> Because they're different.  The SQL spec says that an empty string
> is different from NULL, and so does every database in the world except
> Oracle.  Oracle, however, does not define the standard.

If people would think of it in terms of an address it might make more
sense to them.  An empty string has an address, so can a string, integer
and so on.  When you think of NULL, think of it in the context of a NULL
address.  It's not addressable, it's nothing, it's not set, it's not
there.  I know it's not 100% accurate, but I think it helps folks
understand the concept.

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
  - Benjamin Franklin

Re: NOT NULL Issue

From
Scott Ribe
Date:
Another way is to remember that NULL is a distinguished thing that is
absence of a value, not any value of any type, and this applies to all
types:

- the integer 0 is a value, not null
- the date 1/1/1900 (or 1904 or 0000) is a value, not null
- the time 00:00:00 is a value, not null
- and the string '' is a value, not null


--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice