Re: Insert NULL for ''

Could you instead have a function around it?

into foo (bar) values (myfunction(''))

Where myfunction maps '' to null.

On Fri, 13 Jun 2003, Tom Lane wrote:

> "Daniel Gehrke" <dgehrke@neusta.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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>