Re: How to handle CASE statement with PostgreSQL without need fortypecasting

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.klaver@aklaver.com