Re: Backend dies upon UPDATE of DATE field with CASE WHEN, 6.5.3

"Emils Klotins" <emils(at)mail(dot)usis(dot)bkc(dot)lv> writes:
> 3. If I try the following update in the psql monitor:

> SET DATESTYLE TO 'German';
> UPDATE authors set firstname='dasd',
> lastname='asdadasd',
> birthdate=(CASE WHEN (text '12-12-1956')=(text '') THEN
> NULL ELSE '12-12-1956' END),
> birthplace='asdasd',commentary='',
> lastname_original='asdasd', firstname_original='asdfg',
> birthplace_original='' WHERE author_id=56

Small correction for anyone else trying to reproduce this problem ---
use WHERE author_id=55, or another author_id that is in the given data.

> I get UPDATE 1, however, on all subsequent UPDATEs for that
> row, backend dies with the following error message.

Yes, I see it in 6.5.3. In current sources I get

ERROR: There is no function 'date' with argument #0 of type UNKNOWN

which suggests that the problem is rooted in mis-type-assignment of
the CASE expression. Since you have a NULL and an untyped string
constant as the two possible values of the CASE, it's not too surprising
that the system has some difficulty in figuring out what datatype the
CASE will yield :-(. Presumably this is resulting in bogus data getting
stored into the birthdate field.

I will see to it that this works better in 7.0, but the fix may be too
complex to back-patch into 6.5.3, and I don't have it right now anyway.
As a workaround, try forcing the non-null side of the CASE to be cast
to the right datatype:

birthdate=(CASE WHEN (text '12-12-1956')=(text '') THEN
NULL ELSE '12-12-1956'::date END),
^^^^^^

This seemed to make the problem go away for me.

regards, tom lane