Thread: How to handle CASE statement with PostgreSQL without need for typecasting
Hello,
2. Run statement UPDATE t_update SET F1 = (CASE WHEN (CURRENT_TIMESTAMP IS NULL ) THEN (CURRENT_TIMESTAMP ) ELSE (CURRENT_TIMESTAMP ) END)
Result: No exceptions.
Trying to find a way to perform a CASE statement without needing to typecast. Research so far suggests that this is not possible, but I wanted to check with the PSQL community to be sure.
Steps:
1. CREATE TABLE t_update (F1 timestamp(6) NULL )
1. CREATE TABLE t_update (F1 timestamp(6) NULL )
2. Run statement UPDATE t_update SET F1 = (CASE WHEN (CURRENT_TIMESTAMP IS NULL ) THEN (CURRENT_TIMESTAMP ) ELSE (CURRENT_TIMESTAMP ) END)
Result: No exceptions.
3. Run parametrised statement
UPDATE t_update SET F1 = (CASE WHEN (? IS NULL ) THEN (? ) ELSE (? ) END)
UPDATE t_update SET F1 = (CASE WHEN (? IS NULL ) THEN (? ) ELSE (? ) END)
Result: Error
ERROR: VERROR; column "f1" is of type timestamp without time zone but expression is of type text(Hint You will need to rewrite or cast the expression.; Position 27; File d:\pginstaller.auto\postgres.windows-x64\src\backend\parser\parse_target.c; Line 591; Routine transformAssignedExpr; ) (6822148)
ERROR: VERROR; column "f1" is of type timestamp without time zone but expression is of type text(Hint You will need to rewrite or cast the expression.; Position 27; File d:\pginstaller.auto\postgres.windows-x64\src\backend\parser\parse_target.c; Line 591; Routine transformAssignedExpr; ) (6822148)
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)
This statement works:
UPDATE t_update SET F1 = (CASE WHEN (?::timestamp(6) IS NULL ) THEN (?::timestamp(6) ) ELSE (?::timestamp(6) ) END)
Please let me know your thoughts.
Many thanks!
Anthony
Re: How to handle CASE statement with PostgreSQL without need for typecasting
From
John W Higgins
Date:
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
Re: How to handle CASE statement with PostgreSQL without need fortypecasting
From
Adrian Klaver
Date:
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
Re: How to handle CASE statement with PostgreSQL without need fortypecasting
From
Kyotaro Horiguchi
Date:
Hello.
At Tue, 18 Feb 2020 12:43:21 -0800, Adrian Klaver <adrian.klaver@aklaver.com> wrote in
> 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.
The documentation says:
https://www.postgresql.org/docs/10/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS
> A cast applied to an unadorned string literal represents the initial
> assignment of a type to a literal constant value, and so it will
> succeed for any type (if the contents of the string literal are
> acceptable input syntax for the data type).
..
> However, automatic casting is only done for casts that are marked “OK
> to apply implicitly” in the system catalogs. Other casts must be
> invoked with explicit casting syntax. This restriction is intended to
> prevent surprising conversions from being applied silently.
Conversions from the type unkown is not registered in pg_cast. Also
CREATE CAST on pseudo types like unknown is not allowed.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center