From: | John W Higgins <wishdev(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: How to handle CASE statement with PostgreSQL without need for typecasting |
Date: | 2020-02-18 18:51:46 |
Message-ID: | CAPhAwGwzJ25Dp42RVgSAO2GCSx9eqQhPjuPcnJD5RGzdO+Ox7g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Mercha | 2020-02-18 19:07:44 | Re: Is is safe to use SPI in multiple threads? |
Previous Message | Merlin Moncure | 2020-02-18 18:49:50 | Re: DB running out of memory issues after upgrade |