Re: How to handle CASE statement with PostgreSQL without need for typecasting

Good Morning,

>
> NOTE: From my research online, I found that typecasting works and also
> the error from the database suggests typecasting.
>
> This statement works:
>
> UPDATE t_update SET F1 = (CASE WHEN (?::timestamp(6) IS NULL ) THEN
> (?::timestamp(6) ) ELSE (?::timestamp(6) ) END)
>
>
There is no option to convert the text parameter to a timestamp - you need
to cast it - or use a parsing function or something else - but a text value
cannot drop directly into a timestamp column. But it's not the case
statement that is the issue - but rather the update - so you could shorten
the statement a little with this.

UPDATE t_update SET F1 = (CASE WHEN (? IS NULL ) THEN (?) ) ELSE (?) ) END)
::timestamp(6)

You don't need a timestamp until you place in in the column.

You also probably don't want a case statement here - not the standard
option for this

UPDATE t_update SET F1 = COALESCE(?, ?)::timestamp(6) with the first ?
being the when above and the second being the else above. See here [1]

John

[1] - https://www.postgresql.org/docs/current/functions-conditional.html

>