0

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.

Dmitry
  • 2,626
  • 2
  • 10
  • 15

1 Answers1

0

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

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • 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. – Dmitry May 24 '19 at 15:23