Re: Insert NULL for ''

"Daniel Gehrke" <dgehrke(at)neusta(dot)de> writes:
> Is there any possibility to get postgresql to insert NULL when I do "insert
> into foo (bar) values ('')?

There's no easy way. '' simply is not a legal value of type date.
Therefore, the only way to make the above work is to make the string be
initially considered of type text, and postpone the conversion to date
until after you've checked for the empty-string case.

AFAICS this means you can't fix it with a simple method like a BEFORE
trigger that replaces the value with NULL. The value has to get
converted to type date to form the candidate row that the trigger
receives, so you're too late, the error has already been raised.

I can think of a couple of possible approaches:

* Define a view in which the corresponding column is actually text not
date, say
CREATE VIEW v AS SELECT datecol::text, ...everything else... FROM t;
and then make an ON INSERT rule that transforms an attempted insert
into the view into an insert on the table proper. In this rule you
can put
CASE WHEN NEW.datecol = '' THEN NULL ELSE NEW.datecol::date END
Of course you're also going to need ON UPDATE and ON DELETE rules.

* Give up and make the column be actually type text. Then you just need
an insert/update trigger along the lines of
IF new.datecol is null or new.datecol = '' then
new.datecol = null;
else
new.datecol = new.datecol::date::text;
to ensure that the contents of the column always look like a date.

But both of these answers suck from a maintenance point of view.
I'd honestly recommend that you fix your application. It'll be
less pain in the long run.

regards, tom lane