Re: Insert NULL for ''

Hello,

I am porting an application from MS SQL 7.0 that dynamically creates
statements.
I have a table with a date field. Now I would like postgresql to insert NULL
when I insert '':

insert into foo (bar) values ('');

So in this case I want postgresql to insert NULL instead of giving the error
message "ERROR: Bad date external representation '' "

I know, that the error message is correct and that "insert into foo (bar)
values (NULL)" would be the correct way. But unfortunately I cannot change
the application to do so, therefore my question is:
Is there any possibility to get postgresql to insert NULL when I do "insert
into foo (bar) values ('')?

Daniel Gehrke

> So in this case I want postgresql to insert NULL instead of giving the error
> message "ERROR: Bad date external representation '' "
Use a BEFORE trigger.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

"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

Tom Lane wrote:

> "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 ('')?

...

> I can think of a couple of possible approaches:
>
> * Define a view in which the corresponding column is actually text not
> date

...

> * Give up and make the column be actually type text.

What would be really neat is the ability of CREATE CAST to override
the default implementation and at the same time let the override call
the "base" implementation, if necessary.

Mike Mascari
mascarm(at)mascari(dot)com

Mike Mascari <mascarm(at)mascari(dot)com> writes:
> What would be really neat is the ability of CREATE CAST to override
> the default implementation and at the same time let the override call
> the "base" implementation, if necessary.

That would not help Daniel, because there is no cast here. You've got
an untyped string literal which is going to be directly interpreted as
a constant of the destination column's datatype.

There are any number of easy ways to fix this if we were allowed to
change the SQL being spit out by the application ... but that's exactly
what he doesn't feel he can do.

regards, tom lane

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(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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Jonathan Bartlett <johnnyb(at)eskimo(dot)com> writes:
> Could you instead have a function around it?
> into foo (bar) values (myfunction(''))
> Where myfunction maps '' to null.

That would be an easy solution (one of many) if we could make any
changes in the SQL being emitted by the application, but I think
Daniel is saying that he doesn't want to touch the application at all.

regards, tom lane

Tino Wildenhain wrote:

> Hi,
>
>
> Mike Mascari wrote:
> ...
>
>> What would be really neat is the ability of CREATE CAST to override
>> the default implementation and at the same time let the override call
>> the "base" implementation, if necessary.
>
> This is already possible: you just have to define your own
> type which would behave like a date for all operations
> but not for text2yourdate, which would then accept ''
> for null as well.
>
> Might be a little bit work, buts perfectly doable.

Sure, but client applications, such as ODBC, which understand the SQL
data types won't be able to handle your custom type. And Daniel would
have to modify the schema to use a custom type. I was thinking that
the casting function is called by the database when it gets a string
literal as a text type, but, from reading Tom's response, I guess that
is not the case. If it did, it would have been nice for Daniel to just
override the casting function to handle the NULL mapping of '' for the
DATE datatype. It would also be useful in allowing for customization
of text mappings for the number types as well.

Mike Mascari
mascarm(at)mascari(dot)com

Hi,

Mike Mascari wrote:
...

>
>
> What would be really neat is the ability of CREATE CAST to override
> the default implementation and at the same time let the override call
> the "base" implementation, if necessary.

This is already possible: you just have to define your own
type which would behave like a date for all operations
but not for text2yourdate, which would then accept ''
for null as well.

Might be a little bit work, buts perfectly doable.

Regards
Tino