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
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
