How to Efficiently Update Multiple Rows with NULL Values in PostgreSQL?

Challenges of Updating Multiple Rows with NULL Values

In PostgreSQL, updating multiple rows simultaneously can encounter challenges when dealing with NULL values. When working with standalone VALUES expressions, PostgreSQL lacks information about data types. As a result, explicit casting is required for non-numeric literals, including NULL values.

Casting NULL Values in Update Queries

To overcome this issue, explore the following solutions:

1. Subquery with LIMIT 0

UPDATE foo SET x=t.x, y=t.y FROM
(
  (SELECT pkid, x, y FROM foo LIMIT 0)
   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;

• Pros: Minimal overhead, simple and fast, requires knowledge of relevant column names
• Con: Type resolution can fail for some data types

2. Subquery with LIMIT 0 and SELECT

UPDATE foo SET x=t.x, y=t.y FROM
(
  (SELECT pkid, x, y FROM foo LIMIT 0)
   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;

• Pros: Avoids type resolution failure
• Cons: Verbose syntax per row, slower than VALUES expression for long lists

3. VALUES Expression with Per-Column Type

...
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.
...

• Pros: Typically faster than #2, short syntax for relevant columns, knowledge of only relevant column names required
• Cons: Verbose syntax for few rows, less readable

4. VALUES Expression with Row Type

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;

• Pros: Fastest, shortest syntax for few rows and all columns, no need to specify column names
• Cons: Not well-known syntax, requires knowledge of the number and position of relevant columns in default order

5. VALUES Expression with Decomposed Row Type

...
FROM (
   VALUES
      ((NULL::foo).*)  -- decomposed row of values
    , (1, 20, NULL)    -- uniform syntax for all
    , (2, 50, NULL)
   ) t(pkid, x, y)  -- arbitrary column names (I made them match)
...

• Pros and cons similar to #4, but with more commonly known syntax

The above is the detailed content of How to Efficiently Update Multiple Rows with NULL Values in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!