CODEWITHSUNDEEP
sqlpostgresql
Mj Ebrahimzadeh
Mj Ebrahimzadeh

Reputation: 647

Updating multiple lines in SQL specifies wrong type for null value

Hello i'm trying to update multiple lines in my table and I used this sql but although suggestion is integer nullable but i throws error column suggestion is of type integer but expression is of type text

SQL:

 UPDATE shipment_item AS ass
 SET units_per_box = av.units_per_box,
     boxes = av.boxes,
     box_per_pallet = av.box_per_pallet,
     suggestion = av.suggestion
     FROM (VALUES  (20091,36,2,44,null)  ) AS av
 (id,units_per_box,boxes,
  box_per_pallet,suggestion)

Upvotes: 0

Views: 31

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246798

Any unidentifiable constant like NULL or a string literal in a VALUES statement will be coerced to type text in recent PostgreSQL versions:

SELECT pg_typeof(c3)
FROM (VALUES (1, 1.2, NULL)) AS a(c1,c2,c3);

 pg_typeof 
═══════════
 text
(1 row)

So you should apply an explicit type cast: NULL::integer.

Upvotes: 2

Related Questions