Postgres (ERROR: column "date_deadline" is of type date but expression is of type text)

Question

update res_user set date_of_birth= '1991-07-30' where name = 'USER1';

This work but can update just one row.

In my case I want to update multiple rows with different data in the same query so I wrote that, it doesn't work unfortunately.

Query:

update res_user as ru set
date_of_birth = n.date_of_birth
from (values
    ('1991-07-30','User1'),
    ('1980-06-30','User2'),
    ('1975-02-12','User3'),
) as n(date_of_birth, name)
where n.name = ru.name;

Result:

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

Lukasz Szozda · Accepted Answer · 2018-10-17 19:12:36Z

11

You need to cast:

update res_user as ru set
date_of_birth = n.date_of_birth
from (values
    ('1991-07-30'::date,'User1'),
    ('1980-06-30'::date,'User2'),
    ('1975-02-12'::date,'User3'),
) as n(date_of_birth, name)
where n.name = ru.name;

answered Oct 17, 2018 at 19:12

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres (ERROR: column "date_deadline" is of type date but expression is of type text)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related