Thread: NULL vs. Empty String

Hi All,

Apologies for cross-posting.

I've ported a mod_perl application from Oracle to PosgreSQL, but have run
into a difference that's significant for web work. When I get a form field
submitted, if it's empty, mod_perl assumes it to be an empty string ('').
When I inserted empty strings into Oracle tables, Oracle assumed they were
NULL and inserted them as such. Postgres, OTOH, seems to insert it as an
empty string instead. While I realize that the Posgres interpretation is
perhaps the more precise of the two, it was a feature of Oracle that I
rather liked.

Is there a way to get Posgres to assume that a '' insert should be a NULL
insert? And if not, does anyone know of a way to get mod_perl to make that
assumption instead?

TIA,

David

David Wheeler wrote:
  >Is there a way to get Posgres to assume that a '' insert should be a NULL
  >insert? And if not, does anyone know of a way to get mod_perl to make that
  >assumption instead?

You could write a rule for each table/column that would substitute
NULL for ''.

However, the concept is all wrong.  NULL means "I don't know what
this value is". '' means "I know that this value is an empty string".
Furthermore, having NULLs in columns means you have to be careful
to use ternary logic for every condition (because NULL=x is neither
true nor false).

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Let your light so shine before men, that they may see
      your good works, and glorify your Father which is in
      heaven."         Matthew 5:16

Of course, depending on the application, this could mean hand-modifying
every script... so my apologies if this was not what you were after ;)


> Could it be as simple as:

> $sth = $dbh->prepare("INSERT INTO table (row1, row2) VALUES (?, ?)");
> if ( $webinput eq "" ) { $webinput = undef ; }

> $sth->execute($webinput, $otherstuff);

Could it be as simple as:

$sth = $dbh->prepare("INSERT INTO table (row1, row2) VALUES (?, ?)");
if ( $webinput eq "" ) { $webinput = undef ; }

$sth->execute($webinput, $otherstuff);


I don't have Postgres here to test on, but inserting an undef should
translate to a NULL on most any DBD.



Regards,

Daniel.


----- Original Message -----
From: "David Wheeler" <david@wheeler.net>
To: <pgsql-general@postgresql.org>
Cc: <dbi-users@perl.org>
Sent: Wednesday, March 14, 2001 2:26 PM
Subject: NULL vs. Empty String


Hi All,

Apologies for cross-posting.

I've ported a mod_perl application from Oracle to PosgreSQL, but have run
into a difference that's significant for web work. When I get a form field
submitted, if it's empty, mod_perl assumes it to be an empty string ('').
When I inserted empty strings into Oracle tables, Oracle assumed they were
NULL and inserted them as such. Postgres, OTOH, seems to insert it as an
empty string instead. While I realize that the Posgres interpretation is
perhaps the more precise of the two, it was a feature of Oracle that I
rather liked.

Is there a way to get Posgres to assume that a '' insert should be a NULL
insert? And if not, does anyone know of a way to get mod_perl to make that
assumption instead?

TIA,

David

On Wed, 14 Mar 2001, Oliver Elphick wrote:

> You could write a rule for each table/column that would substitute
> NULL for ''.
>
> However, the concept is all wrong.  NULL means "I don't know what
> this value is". '' means "I know that this value is an empty string".
> Furthermore, having NULLs in columns means you have to be careful
> to use ternary logic for every condition (because NULL=x is neither
> true nor false).

And On Wed, 14 Mar 2001, Steven Lembark wrote:

> Postgress is doing what you want it to -- see anything CJ Date
> has written in the last 20 years.  you are better off inserting
> a default value of '' than NULL -- otherwise you have no idea
> what joins mean.

I completely agree with you two that PostgreSQL does The Right Thing (TM)
vs. what Oracle does. Thus, perhaps I should shift the focus of my queries
to the mod_perl list, since I think it makes sense that empty form fields
submitted from a web page should show up in Perl as undefined (and
therefore will be inserted into Postgres as NULL) rather than as empty
strings.

Thanks for the feedback.

David