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

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

Answer 2

I think you want:

update mytable
set 
    column_a = case when id = 1 then 'FINISH' else 'UNFINISHED' end,
    column_b = case when id = 1 then 1234     else 3124 end
where id in (1, 2)

Rationale:

• the where clause filters only on the ids you want to update, which is more efficient (provided you have an index on id ), and simplifies the conditional logic

• id looks like a number, so it should be treated as such (ie, do not surround the literal values with quotes)

• literal strings must be surrounded with single quotes - double quotes stand for identifiers (such as column names)

Answer 3

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 4

You can use CASE expression.

UPDATE table 
SET 
  column_a = CASE WHEN id = '1' THEN 'FINISH'
                  WHEN id = '2' THEN 'UNFINISH'
                  ELSE 'SOMETHING'
             END,
  column_b = CASE WHEN id = '1' THEN 1234
                  WHEN id = '2' THEN 3124
                  ELSE 0
             END;

Answer 5

In postgresql you can use the update-from-values approach described in the answers here: Update multiple rows in same query using PostgreSQL

And in Go you can implement that like this:

func updateWithSlice(slice []T) error {
    var queryString = `UPDATE "table" AS t SET (
        "column_a"
        , "column_b"
    ) = (
        x."column_a"::text
        , x."column_b"::integer
    )
    FROM (VALUES` // `

    numColumns := 3 // the number of columns you want to update + 1 for the id column
    params := make([]interface{}, len(slice)*numColumns)
    for i, t := range slice {
        pos := i * numColumns

        params[pos+0] = t.ColumnA
        params[pos+1] = t.ColumnB
        params[pos+2] = t.Id

        queryString += `($` + strconv.Itoa(pos+1) +
            `,$` + strconv.Itoa(pos+2) +
            `,$` + strconv.Itoa(pos+3) +
            `),`
    }

    queryString = queryString[:len(queryString)-1] // drop last ","
    queryString += ` ) AS x (
        "column_a"
        , "column_b"
        , "id"
    )
    WHERE t."id" = x."id"::integer` // `

    _, err := db.Exec(queryString, params...)
    return err
}