PostgreSQL Update From Values Treats Nulls As Text [duplicate]

Question

Need help in solving an issue when one of the values' columns contains only Null values. Example:

create table foo (
    id serial constraint foo_pk primary key,
    a int,
    b int
);

insert into foo (a,b) values (1, 1), (2, 2);

update foo t
set a = v.a, b = v.b
from (values (1, NULL, 1),(2, NULL, 2)) as v(id, a, b)
where v.id = t.id;

This gives me:

[42804] ERROR: column "a" is of type integer but expression is of type text 
Hint: You will need to rewrite or cast the expression. Position: 22

I'm using psycopg2.extras.execute_values in order to update multiple Django ORM objects. Looking for a solution that doesn't need to cast nulls to field types explicitly.

Great! This is exactly what I was looking for. Used the solution #5. Thank you! — Dmitry
– Dmitry, Commented May 24, 2019 at 16:04

Kaushik Nayak · Accepted Answer · 2019-05-24 03:36:00Z

1

As all the values you've passed for column a are NULLs , Postgres will implicitly consider them to be TEXTs

You have 2 options,

cast the value of a to int in the SET expression.

set a = v.a::int

OR

cast one of the values of a to INT, from which the other values will default to.

values (1, NULL::int, 1),(2, NULL, 2)) as v(id, a, b)

DEMO

answered May 24, 2019 at 3:36

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

1 Comment

Dmitry Over a year ago

Thank you for your reply Kaushik. Sorry, I didn't mention earlier that I need a solution that works without explicit cast. Like this: sql update foo set a = null, b = null where id = 1; As update statements are being constructed automatically based on ORM objects passed into a function.

Collectives™ on Stack Overflow

PostgreSQL Update From Values Treats Nulls As Text [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related