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