PostgreSQL: update multiple records (handle NULL values)

postgresql

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

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)}

PostgreSQL: update multiple records (handle NULL values)

Related

Recent Posts