Обсуждение: How to handle CASE statement with PostgreSQL without need for typecasting

Поиск
Список
Период
Сортировка

How to handle CASE statement with PostgreSQL without need for typecasting

От
Anthony Hall
Дата:
Hello,

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 )

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)

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)  


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)   


Please let me know your thoughts.

Many thanks!
Anthony

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

От
John W Higgins
Дата:
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

От
Adrian Klaver
Дата:
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

От
Kyotaro Horiguchi
Дата:
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