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

On 2/18/20 10:51 AM, John W Higgins wrote:
> 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

Yes and no:

test=> UPDATE t_update SET F1 = '02/23/2020';
UPDATE 1

UPDATE 1
test=> select pg_typeof('02/23/2020');
pg_typeof
-----------
unknown

test=> UPDATE t_update SET F1 = '02/23/2020'::unknown;
UPDATE 1

test=> UPDATE t_update SET F1 = (CASE WHEN ('02/18/2020' IS NULL ) THEN
('02/18/2020' ) ELSE ('02/19/2020') END)::unknown;
ERROR: failed to find conversion function from unknown to timestamp
without time zone

test=> UPDATE t_update SET F1 = (CASE WHEN ('02/18/2020' IS NULL ) THEN
('02/18/2020' ) ELSE ('02/19/2020') END)::timestamp;
UPDATE 1

So there is some sort of different evaluation going on in the CASE
statement.

> 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
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com