PostgreSQL: update multiple records (handle NULL values)

Question

I'm working with PostgreSQL and was looking for a way to update multiple records in a single query. I found this solution: Update multiple rows in same query using PostgreSQL

But this solution does not work when all rows to update should be updated with NULL value. Here is an example:

Table definition

create table person (id char(1), my_value smallint);

populate

insert into person values
   ('1', 1),
   ('2', NULL),
   ('3', 3)
;

not working update

update person as db_record_to_update set
   my_value = db_record_new.my_value
from (values
   ('1', NULL),
   ('2', NULL)
) as db_record_new(id, my_value) 
where db_record_new.id = db_record_to_update.id;

Error I get

DatatypeMismatch: column "my_value" is of type smallint but expression is of type text
LINE 3:    my_value = db_record_new.my_value
                      ^
HINT:  You will need to rewrite or cast the expression.

Question

How can I execute multiple update with one / multiple fields all null ?

Note

the query is valid and works fine only if at least one my_value is non null. For instance this query works fine (same expet I replaced 1 NULL to 55):

update person as db_record_to_update set
   my_value = db_record_new.my_value
from (values
   ('1', NULL),
   ('2', 55)
) as db_record_new(id, my_value) 
where db_record_new.id = db_record_to_update.id;

I am running this queries on a notebook with psychog2 in case it matters

Or if you don't want to do this for every value, ` db_record_new(id, my_value::int)` or my_value = db_record_new.my_value::int. Basically following HINT: You will need to rewrite or cast the expression.. Since NULL can be anything you have to give the parser a hint about what 'type' of NULL you are trying to INSERT/UPDATE. Your second case worked because the 55 provided the type hint. — Adrian Klaver
– Adrian Klaver, Commented Jan 12, 2022 at 17:10

Bergi · Accepted Answer · 2022-01-12 20:05:12Z

1

The problem is that Postgres doesn't know the type of the my_value column in your VALUES expression, so it defaults them to text. You can avoid this by annotating at least one NULL value with the desired type smallint:

update person as db_record_to_update
set my_value = db_record_new.my_value
from (values
   ('1', NULL::smallint),
   ('2', NULL)
) as db_record_new(id, my_value) 
where db_record_new.id = db_record_to_update.id;

^{(online demo)}

answered Jan 12, 2022 at 20:05

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: update multiple records (handle NULL values)

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