Performing a multi-valued update in postgres with dates-CodePudding

I am performing a multi-valued update in postgres. However, the datatype DATE in postgres is giving me issues. I got the following code to perform the update, but it gives an error

update users as u set
  id = u2.id,
  appointment = u2.appointment
from (values
  (1, '2022-12-01'),
  (2, '2022-12-01')
) as u2(id, appointment)
where u2.id = u.id;

ERROR:  column "appointment" is of type date but expression is of type text
LINE 3:   appointment = u2.appointment
                        ^
HINT:  You will need to rewrite or cast the expression.

Normally postgres accepts dates in such a format, how should I perform this update?

CodePudding user response：

Postgres first casts the date values as string when creating u2, meaning it cannot cast them as date again when performing the actual update. Cast the dates as date to solve the issue:

update users as u set
  id = u2.id,
  appointment = u2.appointment
from (values
  (1, CAST('2022-12-01' as date)),
  (2, CAST('2022-12-01' as date))
) as u2(id, appointment)
where u2.id = u.id;

SQLFiddle: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=9f30e708c8508a2df94458b70399eb3a