pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNull not equal to '' (empty)
4+ messages / 4 participants
[nested] [flat]
* Null not equal to '' (empty)
@ 2002-09-20 06:09 Ajit Aranha <ajit_aranha@rocketmail.com>
2002-09-27 12:36 ` Re: Null not equal to '' (empty) Richard Huxton <dev@archonet.com>
2002-09-27 12:40 ` Re: Null not equal to '' (empty) Ries van Twisk <ries@jongert.nl>
2002-09-27 16:16 ` Re: Null not equal to '' (empty) Josh Berkus <josh@agliodbs.com>
0 siblings, 3 replies; 4+ messages in thread
From: Ajit Aranha @ 2002-09-20 06:09 UTC (permalink / raw)
To: pgsql-sql
Why is ''(empty) not equal to null? Its a major headache when porting
from other RDBMS like Oracle. Anyone knows any easy workarounds?
i.e. if you use: create table tbl (
c1 varchar(5));
insert into tbl values ('');
select * from tbl where c1 is null; will return
zero rows.
Also try this: select TO_DATE('','DD-MM-YY'); and
this TO_DATE('','DD-Mon-YY');
;-)
-Ajit (ajit_aranha@rocketmail.com)
__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Null not equal to '' (empty)
2002-09-20 06:09 Null not equal to '' (empty) Ajit Aranha <ajit_aranha@rocketmail.com>
@ 2002-09-27 12:36 ` Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2002-09-27 12:36 UTC (permalink / raw)
To: Ajit Aranha <ajit_aranha@rocketmail.com>; pgsql-sql
On Friday 20 Sep 2002 7:09 am, Ajit Aranha wrote:
> Why is ''(empty) not equal to null? Its a major headache when porting
> from other RDBMS like Oracle. Anyone knows any easy workarounds?
By definition it is different - null means "not known" or "no value" not empty
string. Do you think it should be zero for numbers?
> i.e. if you use: create table tbl (
> c1 varchar(5));
> insert into tbl values ('');
> select * from tbl where c1 is null; will return
> zero rows.
Yep - that's the way it should be. If you want empty-strings, ask for them. If
you don't want to allow null values in a column define it as NOT NULL.
- Richard Huxton
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Null not equal to '' (empty)
2002-09-20 06:09 Null not equal to '' (empty) Ajit Aranha <ajit_aranha@rocketmail.com>
@ 2002-09-27 12:40 ` Ries van Twisk <ries@jongert.nl>
2 siblings, 0 replies; 4+ messages in thread
From: Ries van Twisk @ 2002-09-27 12:40 UTC (permalink / raw)
To: 'Ajit Aranha' <ajit_aranha@rocketmail.com>; pgsql-sql
This is because '' is not equal to NULL
'' Means a empty string
NULL means a empty set
So this: SELECT * FROM tbl WHERE c1 IS NULL;
is totally different then: SELECT * FROM tbl WHERE c1='';
Ries
-----Oorspronkelijk bericht-----
Van: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]Namens Ajit Aranha
Verzonden: vrijdag 20 september 2002 8:09
Aan: pgsql-sql@postgresql.org
Onderwerp: [SQL] Null not equal to '' (empty)
Why is ''(empty) not equal to null? Its a major headache when porting
from other RDBMS like Oracle. Anyone knows any easy workarounds?
i.e. if you use: create table tbl (
c1 varchar(5));
insert into tbl values ('');
select * from tbl where c1 is null; will return
zero rows.
Also try this: select TO_DATE('','DD-MM-YY'); and
this TO_DATE('','DD-Mon-YY');
;-)
-Ajit (ajit_aranha@rocketmail.com)
__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Null not equal to '' (empty)
2002-09-20 06:09 Null not equal to '' (empty) Ajit Aranha <ajit_aranha@rocketmail.com>
@ 2002-09-27 16:16 ` Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2002-09-27 16:16 UTC (permalink / raw)
To: Ajit Aranha <ajit_aranha@rocketmail.com>; pgsql-sql
Ajit,
> Why is ''(empty) not equal to null? Its a major headache when
> porting
> from other RDBMS like Oracle.
'' is not equal to NULL because that is the ANSI SQL92 and SQL99
international specification. The fact that other databases fail to
follow the specification (and '' = NULL is *not* standard Oracle 8
behavior, either, so I don't know what you are porting from ... MS
Access?) is not our concern.
NULL is not equal to *anything*, including itself. Nor is it greater
or less than anything. In fact, any operation involving NULL should
result in NULL. This is the SQL spec, becuase NULL represents
"unknown" and thus cannot be evaluated.
For my intranet applications, I wrote a set of functions called
"is_empty(data)" since my web programmer is rather liberal in
substituting NULL for '' or for '0' or whatever. They go like this:
CREATE FUNCTION is_empty( VARCHAR ) RETURNS BOOLEAN AS '
SELECT $1 IS NULL OR BTRIM($1) = '';
' LANGUAGE 'sql' WITH (ISCACHABLE);
CREATE FUNCTION is_empty( NUMERIC ) RETURNS BOOLEAN AS '
SELECT $1 IS NULL OR $1 = 0::NUMERIC;
' LANGUAGE 'sql' WITH (ISCACHABLE);
etc.
This will give you an all-purpose "empty value" detector.
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-09-27 16:16 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-20 06:09 Null not equal to '' (empty) Ajit Aranha <ajit_aranha@rocketmail.com>
2002-09-27 12:36 ` Richard Huxton <dev@archonet.com>
2002-09-27 12:40 ` Ries van Twisk <ries@jongert.nl>
2002-09-27 16:16 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox