Date: Mon, 7 Jun 2004 07:34:53 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: sad <sad@bankir.ru>
Cc: pgsql-sql@postgresql.org
Subject: Re: empty string casting to typed value
In-Reply-To: <200406071037.44547.sad@bankir.ru>
Message-ID: <20040607071120.Y17344@megazone.bigpanda.com>
References: <200406071037.44547.sad@bankir.ru>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Mon, 7 Jun 2004, sad wrote:

> It is clear that '' is a bad integer or timestamp representation
>
> but during the user input NULLs are usually represented with empty strings
> sometimes bunch of  'if empty' instructions grows huge
> (and in case of casting to timestamp apostrophes make sense)


> Why you prohibit casting ''::int  to NULL ?

In part, this is for spec complience. The spec pretty explicitly says that
casting something that does not look like a signed numeric literal to a
numeric type results in an error and empty string does not look like a
signed numeric literal.

In part, it is for the fact that doing such conversions opens the doors to
alot of odd behavior depending on how it is defined.  For example, if the
cast that effectively happens on insert is allowed to do that conversion,
a phrase like VALUES ('', '', '') may insert 0-3 nulls into a table
depending on the datatypes since ''=>NULL shouldn't happen for character
types. If it's allowed to happen on implicit casts in expressions,
a phrase like WHERE col != '' may silently be written into something
meaningless (col != NULL) for some types.  Even when limited to explicit
casts only, it blurs the line with non-NULL values and will make people
confused when it doesn't occur for character types.

Finally, you should be able to write functions that take a text argument
and return an integer, datetype, whatever that return NULL for empty
string and the value cast to integer for others so there's probably not
much reason to break the cast semantics.