Eliminating Code Duplication in PostgreSQL with the EXCLUDED Clause and jOOQ's UpdatableRecord

Understanding Duplicated Set Statements in PostgreSQL

As a developer, have you ever found yourself staring at a seemingly endless string of duplicated set statements in your PostgreSQL queries? Perhaps you’re working on an insert and update clause, where you need to perform both operations simultaneously. In this article, we’ll explore how to factor out these duplicated set statements into a shared block of code.

A Common Problem

Let’s examine the provided example query:

db.insertInto(RAID_V2).
  set(RAID_V2.HANDLE, handle).
  set(RAID_V2.SERVICE_POINT_ID, servicePointId).
  set(RAID_V2.URL, raidUrl).
  set(RAID_V2.URL_INDEX, urlContentIndex).
  set(RAID_V2.PRIMARY_TITLE, primaryTitle).
  set(RAID_V2.METADATA, jsonbMetadata).
  set(RAID_V2.METADATA_SCHEMA, mapJs2Jq(metadata.getMetadataSchema())).
  set(RAID_V2.START_DATE, startDate).
  set(RAID_V2.DATE_CREATED, LocalDateTime.now()).
  set(RAID_V2.CONFIDENTIAL, confidential).
onConflict(RAID_V2.HANDLE).doUpdate().
    set(RAID_V2.SERVICE_POINT_ID, servicePointId).
    set(RAID_V2.URL, raidUrl).
    set(RAID_V2.URL_INDEX, urlContentIndex).
    set(RAID_V2.PRIMARY_TITLE, primaryTitle).
    set(RAID_V2.METADATA, jsonbMetadata).
    set(RAID_V2.METADATA_SCHEMA, mapJs2Jq(metadata.getMetadataSchema())).
    set(RAID_V2.START_DATE, startDate).
    set(RAID_V2.DATE_CREATED, LocalDateTime.now()).
    set(RAID_V2.CONFIDENTIAL, confidential).
where(RAID_V2.HANDLE.eq(handle)).
execute();

In this query, the set statements are duplicated in both the insert and update clauses. This can lead to code duplication, maintenance issues, and make it harder to modify or extend the query.

The Solution: PostgreSQL’s EXCLUDED Clause

To solve this problem, you can utilize PostgreSQL’s EXCLUDED clause, which was introduced in jOOQ 3.17. The EXCLUDED clause allows you to reference columns from the MERGE clause that are not present in the INSERT clause.

Using EXCLUDED in INSERT and UPDATE Clauses

Here’s an example of how to use the EXCLUDED clause:

.onConflict(RAID_V2.HANDLE).doUpdate()
.set(RAID_V2.SERVICE_POINT_ID, excluded(RAID_V2.SERVICE_POINT_ID))
.set(RAID_V2.URL, excluded(RAID_V2.URL))
...

In this example, the EXCLUDED clause is used to reference columns from the MERGE clause that are not present in the INSERT clause. The duplicated set statements are now factored out into a shared block of code.

Automatically Excluding Key Columns

If you’re concerned about repeating all columns, you can use the following approach:

.onConflict(RAID_V2.HANDLE).doUpdate()
.set(Arrays.stream(RAID_V2.fields())
           .filter(f -&gt; !isKey(f)) // Exclude key columns, and others...
           .collect(Collectors.toMap(f -&gt; f, f -&gt; excluded(f))))

In this example, the EXCLUDED clause is used to exclude key columns and other columns that you don’t want to update.

Alternative Approach: UpdatableRecord::merge

In simpler cases, you can use jOOQ’s UpdatableRecord::merge method to simplify the upsert operation:

.onConflict(RAID_V2.HANDLE)
  .doUpdate()
  .set(UpdatableRecord::merge(RAID_V2))

This approach eliminates the need for duplicated set statements and provides a more concise way to perform upsert operations.

Conclusion

By utilizing PostgreSQL’s EXCLUDED clause, you can factor out duplicated set statements into a shared block of code. This not only reduces code duplication but also makes it easier to maintain and modify your queries.

In addition to the EXCLUDED clause, jOOQ provides alternative approaches, such as using UpdatableRecord::merge, to simplify upsert operations.

References

[jOOQ Documentation: EXCLUDED Clause](https://www.jooq.org/doc/3.17/user manual/sql- clauses/EXCLUDED)
[jOOQ Documentation: UpdatableRecord::merge](https://www.jooq.org/doc/3.17/user manual/sql-builder/updaterecord-merge)

Last modified on 2024-07-02