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.* 
Top comments (0)