using coalesce and dynamic updates to multiple records in postgres

2019-07-16

~4 min read

711 words

I wanted to update multiple fields on multiple records and handle the case that not all of the information is present in the update for each record.

Basically, I want to patch not put and when it gets to Postgres, I don’t want those empty values to be set to null.

A regular UPDATE, however, would do exactly that - overwrite my values with null if I didn’t supply them. Fortunately, Postgres comes with COALESCE which can accommodate this sort of situation. Let’s take a look.

I’ll use the same example as I did Using Typescript’s Pick to Improve Communication and Decrease Maintenance

A refresher, the table is defined as:

interface IMyTable {
  id: string
  foreign_id: string
  name: string

  is_enabled: boolean
  is_custom: boolean
  display_order?: number

  name_en: string
  name_sp?: string
  name_fr?: string

  description_en?: string
  description_sp?: string
  description_fr?: string
}

The columns I’m looking to update again are is_enabled and display_order

An example request body for the patch might be:

[
  {
    "id": "427001",
    "is_enabled": true
  },
  {
    "id": "427002",
    "display_order": 2
  },
  {
    "id": "427003",
    "is_enabled": true,
    "display_order": 3
  }
]

Single Record Update

If I was just trying to update one record at a time, I would have more options about conditional statements to append to my query.

async changeMyTable(proposal: readonly MyTableProposal): Promise<...> {

  const updateQuery = SQL`
    WITH proposed_vals (id, is_enabled, display_order) AS (VALUES (${proposal.id}, ${proposal.is_enabled}, ${proposal.display_order})`
    UPDATE my_table AS t
    SET `;
  if( proposal.is_enabled ) updateQuery.append(SQL`is_enabled = COALESCE( CAST (p.is_enabled as bool), CAST(t.is_enabled as bool) )`)
  if( proposal.display_order ) updateQuery.append(SQL`, display_order = COALESCE( CAST (p.display_order as int4), CAST (t.display_order as int4) )`)
  updateQuery.append(SQL`
    FROM proposed_vals AS p
    WHERE
      t.id = p.id
      and t.end_version is null
    RETURNING
       t.*
    `);

  const data = await this._pool.query(updateQuery);
  return data.rows;
}

NB: this will throw a syntax error if display_order is present without an is_enabled

Handling Multiple Records With Ambiguity

So, how could this work with multiple records?

With multiple records, I’m using the update...with syntax:

This is where the COALESCE statement comes in. Instead of having to understand in advance if the element is present, we can use the initial value as a fail-safe. If the field is not present, we will use the value that’s in place.

My initial attempt to use COALESCE led to:

async changeMyTable(proposal: readonly MyTableProposal[]): Promise<...> {
  const valuesToUpdate = helpers.SQLJoinStatement( /* ... */ )
  const updateQuery = SQL`WITH proposed_vals (id, is_enabled, display_order) AS (VALUES `
  .append(valuesToUpdate)
  .append(SQL` )
     UPDATE my_table AS t
     SET is_enabled = COALESCE( p.is_enabled, t.is_enabled )
     , display_order = COALESCE( p.display_order, t.display_order )
     FROM proposed_vals AS p
     WHERE
       t.id = p.id
       and t.end_version is null
     RETURNING
        t.*
     ;`);

  const data = await this._pool.query(updateQuery);
  return data.rows;
}

This, however, threw the error: 500 COALESCE types text and boolean cannot be matched.

Researching, I found that I could get past this problem with a CAST to ensure the type. I felt comfortable doing this because I knew my proposal was typed appropriately and I’d built the table.

async changeMyTable(proposal: readonly MyTableProposal[]): Promise<...> {
  const valuesToUpdate = helpers.SQLJoinStatement( /* ... */ )
  const updateQuery = SQL`WITH proposed_vals (id, is_enabled, display_order) AS (VALUES `
  .append(valuesToUpdate)
  .append(SQL` )
     UPDATE my_table AS t
     SET is_enabled = COALESCE( CAST (p.is_enabled AS BOOL), CAST(t.is_enabled AS BOOL) )
     , display_order = COALESCE( CAST (p.display_order AS INT4), CAST (t.display_order AS INT4) )
     FROM proposed_vals AS p
     WHERE
       t.id = p.id
       AND t.end_version IS NULL
     RETURNING
        t.*
     ;`);

  const data = await this._pool.query(updateQuery);
  return data.rows;
}

With another set of eyes, I was able to refine this again. The issue is that the values in my proposed_vals implicitly have a type of text or number. If I wanted it to be something separate, I needed to alert Postgres of that.

Instead of the CAST (value AS type) I can use the more terse notation of the double colon :: and apply it only to the proposed values.

UPDATE metadata_lookupvalues AS luv
  SET is_enabled = COALESCE( p.is_enabled::BOOL, luv.is_enabled )
    , display_order = COALESCE( p.display_order::INT4, luv.display_order )
  FROM proposed_vals AS p
  WHERE
    luv.id = p.id
    AND luv.end_version IS NULL
  RETURNING
    luv.*

Hi there and thanks for reading! My name's Stephen. I live in Chicago with my wife, Kate, and dog, Finn. Want more? See about and get in touch!

using coalesce and dynamic updates to multiple records in postgres

Single Record Update

Handling Multiple Records With Ambiguity

Related