Jooq Postgres Complex Upserts

[Samir Faci]

I initially started this conversation off list because the query wasn't something I was sure I could share publicly.  I re-wrote the example query and wanted to share the response for anyone else that could benefit.

---

This is one of the more complex Jooq use cases I had to support.  Let's say the table schema was something like this.

table_name:  user

id <serial id>

first_name <TEXT>,

last_name <TEXT>, 

address <TEXT>,

employed CHAR(1)

start_date TIMESTAMP,

salary <NUMERIC>

It's probably not 3NF, but i'm using it as an example.  I'll add that the serial ID is the PKey but was a bad design choice by a fictional DBA creating a fictional table, and I have a unique constraint of first_name + last_name that we'll pretend is truly unique and is the actually PKey for my use case.

I'm migrating data from one table to another where some of the data has already been copied over so I need to use an upsert.

WITH subquery AS (
    SELECT DISTINCT
      first_name,
      last_name,
      address,
      CASE WHEN employed = 'Y'
        THEN TRUE::boolean
      ELSE FALSE::boolean  END AS employed,
        start_date,
        salary
    FROM user_table
    WHERE start_date > '2011-01-01'
)
INSERT INTO improved_user_table (SELECT *
                                 FROM subquery)
ON CONFLICT (first_name, last_name)
  DO UPDATE
    SET address  = EXCLUDED.address,
      employed   = EXCLUDED.employed,
      start_date = EXCLUDED.start_date,
      salary     = EXCLUDED.salary
      WHERE
      (user_table.address <> EXCLUDED.address OR
       user_table.employed <> EXCLUDED.employed OR
       user_table.start_date <> EXCLUDED.start_date OR
       user_table.salary <> EXCLUDED.salary);

--------

BEGIN Lukas's Response

Regarding your question:

- Currently you cannot prepend a WITH clause to an INSERT statement. I was sure there was a pending feature request, but I couldn't find it, so I created one just as a reminder: https://github.com/jOOQ/jOOQ/issues/5723. The workaround here would be to use a derived table rather than a CTE.

- The EXCLUDED pseudo table currently isn't supported explicitly by jOOQ (https://github.com/jOOQ/jOOQ/issues/5214), but you can easily work around this limitation with plain SQL or simple aliasing.

- ON CONFLICT will be supported in jOOQ 3.9

- ON CONFLICT's WHERE clause will be supported in jOOQ 3.10 (https://github.com/jOOQ/jOOQ/issues/5637)

So, I guess, for this particular case that makes heavy use of PostgreSQL vendor-specific features, jOOQ is currently too limited...

END Luka's Response

---

Hope this helps anyone else who ran into a similar use case.

--

Thank you
Samir Faci

https://keybase.io/csgeek