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

Поиск
Список
Период
Сортировка
От John W Higgins
Тема Re: How to handle CASE statement with PostgreSQL without need for typecasting
Дата
Msg-id CAPhAwGwzJ25Dp42RVgSAO2GCSx9eqQhPjuPcnJD5RGzdO+Ox7g@mail.gmail.com
обсуждение исходный текст
Ответ на How to handle CASE statement with PostgreSQL without need for typecasting  (Anthony Hall <eat.drink.be.merry@gmail.com>)
Ответы Re: How to handle CASE statement with PostgreSQL without need fortypecasting
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Anthony Hall
Дата:
Сообщение: How to handle CASE statement with PostgreSQL without need for typecasting
Следующее
От: Tom Mercha
Дата:
Сообщение: Re: Is is safe to use SPI in multiple threads?