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

Hello.

At Tue, 18 Feb 2020 12:43:21 -0800, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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