Okay, thank you for the update. And yes, absolutely right, that insert does have to be in a CTE. I just flubbed the example.
-Daniel On Mon, Jul 3, 2017 at 10:29 AM Lukas Eder <[email protected]> wrote: > Hi Daniel, > > This is, very unfortunately, not doable yet. The relevant issue is this > one here: > https://github.com/jOOQ/jOOQ/issues/3185 > > There is currently no simple workaround, short of resorting to > constructing the outer query with plain SQL, and wrapping the nested > queries in templates. > > Note, I think that your suggested syntax isn't possible in PostgreSQL > either. You'd have to put your "ins" query in a common table expression: > > WITH ins AS ( > > INSERT INTO foo(natural_key) > VALUES ('aKey') > ON CONFLICT DO NOTHING > RETURNING id > ) > > SELECT id > FROM ins > > WHERE id IS NOT NULL > UNION DISTINCT > SELECT id > FROM foo > WHERE natural_key = 'aKey'; > > > Thanks, > Lukas > > 2017-07-03 15:52 GMT+02:00 Daniel Einspanjer < > [email protected]>: > >> I'm trying to build the following query in the DSL: >> >> >> SELECT id >> FROM ( >> INSERT INTO foo(natural_key) >> VALUES ('aKey') >> ON CONFLICT DO NOTHING >> RETURNING id >> ) ins >> WHERE id IS NOT NULL >> UNION DISTINCT >> SELECT id >> FROM foo >> WHERE natural_key = 'aKey'; >> >> >> I'm having trouble getting a TableLike object out of the insert, with or >> without the ON CONFLICT. Is this doable? >> > -- >> 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. >> > -- > You received this message because you are subscribed to a topic in the > Google Groups "jOOQ User Group" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/jooq-user/QsjcRa8NnnY/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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.
