pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Josh Berkus <josh@agliodbs.com>
To: Ajit Aranha <ajit_aranha@rocketmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Null not equal to '' (empty)
Date: Fri, 27 Sep 2002 09:16:08 -0700
Message-ID: <web-1731902@davinci.ethosmedia.com> (raw)
In-Reply-To: <20020920060916.42881.qmail@web14104.mail.yahoo.com>

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

view thread (4+ messages)

Message-ID: <web-1731902@davinci.ethosmedia.com>
Permalink:  ../web-1731902@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-1731902@davinci.ethosmedia.com

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: josh@agliodbs.com, ajit_aranha@rocketmail.com
  Subject: Re: Null not equal to '' (empty)
  In-Reply-To: <web-1731902@davinci.ethosmedia.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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