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, Jan 12 '22 at 17:10

score 1 · Accepted Answer · answered Jan 12 '22 at 20:05

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)

1 Answers1