Bulk Update multiple rows in same query using PostgreSQL

Question

I'm looking to update multiple rows in PostgreSQL and Go in one statement. Is there a way to do something like the following?

UPDATE table 
SET column_a = "FINISH", 
    column_b = 1234 
WHERE id = '1',
    column_a = "UNFINISH", 
    column_b = 3124 
WHERE id = '2' 

and is there an example, if executed in go language?

Answer 1

I am a fan of structuring this using a derived table:

UPDATE t
    SET column_a = v.column_a,
        column_b = v.column_b
    FROM (VALUES (1, 'FINISH', 1234),
                 (2, 'UNFINISH', 3124)
         ) v(id, column_a, column_b)
    WHERE v.id = t.id;

This makes it simple to add additional values -- both ids and columns -- without complicating the query. It is less prone to accidental errors as well.

Answer 2

The most efficient way to do this in Postgres is using UNNEST. This lets you pass one parameter per column (rather than number of rows x number of columns). For your example, this would look like:

UPDATE table
SET
  column_a=bulk_query.updated_column_a,
  column_b=bulk_query.updated_column_b
FROM
  (
    SELECT * FROM UNNEST(
      ?::TEXT[],
      ?::TEXT[],
      ?::INT[]
    ) AS t(id, updated_column_a, updated_column_b)
  ) AS bulk_query
WHERE
  users.id=bulk_query.id

Then you can pass three parameters:

[
  ["1", "2"],
  ["FINISH", "UNFINISH"],
  [1234, 3124]
]

The great thing about this approach, is that you only need those 3 parameters no matter how many rows you want to update.

I've written more about this topic in this blog post: https://www.atdatabases.org/blog/2022/01/21/optimizing-postgres-using-unnest#update-multiple-records-to-different-values-in-a-single-query