How to Handle NULL Values in PostgreSQL Multi-Row Updates?

Casting NULL Values in Multi-Row Updates

Updating multiple rows with NULL values in PostgreSQL can lead to errors due to the absence of type data forstandalone VALUES expressions. Here are some solutions to overcome this issue:

1. Select Row with LIMIT 0, Append Rows with UNION ALL VALUES

This method uses a LIMIT 0 subquery to retrieve column names and types from the table. This defines the row type, which is then used to cast the updated values.

UPDATE foo f
SET    x = t.x
     , y = t.y
FROM  (
  (SELECT pkid, x, y FROM foo LIMIT 0) -- parenthesis needed with LIMIT
   UNION ALL
   VALUES
      (1, 20, NULL)  -- no type casts here
    , (2, 50, NULL)
   ) t               -- column names and types are already defined
WHERE  f.pkid = t.pkid;

2. Select Row with LIMIT 0, Append Rows with UNION ALL SELECT

Similar to the previous method, but uses a SELECT to append rows instead of a VALUES expression, avoiding potential type resolution issues.

UPDATE foo f
SET    x = t.x
     , y = t.y
FROM  (
  (SELECT pkid, x, y FROM foo LIMIT 0) -- parenthesis needed with LIMIT
   UNION ALL SELECT 1, 20, NULL
   UNION ALL SELECT 2, 50, NULL
   ) t               -- column names and types are already defined
WHERE  f.pkid = t.pkid;

3. VALUES Expression with Per-Column Type

This approach uses a row of NULL values as the first row of a VALUES expression, effectively defining the column types. Subsequent rows can be updated without explicit casting.

...
FROM  (
   VALUES 
     ((SELECT pkid FROM foo LIMIT 0)
    , (SELECT x    FROM foo LIMIT 0)
    , (SELECT y    FROM foo LIMIT 0))  -- get type for each col individually
   , (1, 20, NULL)
   , (2, 50, NULL)
   ) t (pkid, x, y)  -- columns names not defined yet, only types.
...

4. VALUES Expression with Row Type

This method uses row types to define column types implicitly. The row is cast to a row type representing the table, and individual columns can be accessed using field selection.

UPDATE foo f
SET x = (t.r).x         -- parenthesis needed to make syntax unambiguous
  , y = (t.r).y
FROM (
   VALUES
      ('(1,20,)'::foo)  -- columns need to be in default order of table
     ,('(2,50,)')       -- nothing after the last comma for NULL
   ) t (r)              -- column name for row type
WHERE  f.pkid = (t.r).pkid;

5. VALUES Expression with Decomposed Row Type

Similar to the previous method, but uses decomposed row values in standard syntax.

UPDATE foo f
SET    x = t.x
     , y = t.y
FROM (
   VALUES
      (('(1,20,)'::foo).*)  -- decomposed row of values
    , (2, 50, NULL)
   ) t(pkid, x, y)  -- arbitrary column names (I made them match)
WHERE  f.pkid = t.pkid;     -- eliminates 1st row with NULL values

Conclusion

While selecting row with LIMIT 0 is a fast and widely used method, it can fail if type resolution is not possible for some values. The other methods provide alternative approaches with performance implications varying based on the number of columns and rows involved. Ultimately, the choice of method depends on the specific requirements and compatibility with existing code.

The above is the detailed content of How to Handle NULL Values in PostgreSQL Multi-Row Updates?. For more information, please follow other related articles on the PHP Chinese website!