On Thu, Nov 3, 2016 at 5:35 AM, Lukas Eder <[email protected]> wrote:
> > > 2016-11-02 4:29 GMT+01:00 Samir Faci <[email protected]>: > >> >> Yup. That's what I was using. The issue I was running into was that the >> PKey was not useful, so I needed to specify a unique constraint/index. It >> looks like that will be supported in 3.9. >> > > I'm curious about this to learn more about your use-case. Why was the > primary key not useful in your case? > Well, It was just a badly designed schema. Where the PKey is a serial auto incrementing ID, while what uniquely identifies the record are other columns in the table. Let's take something like this as an example: id, user_id, bonus_type, year, bonus_value. so the true key would be something like user_id_bonus_type, year but the actual primary key is id. so the intended behavior is that if I insert a record with (user_id, bonus_type, year) and there is an existing record we should update the record rather then inserting another one. But since 'id' is the actual key it won't ever conflict on ID since that is just a sequence. in which case my SQL would end up having a constraint / index on (user_id, bonus_type, year) and my SQL would end up being something like insert into bonus_table (id, user_id, bonus_type, year, bonus_value) values (....) on conflict (user_id, bonus_type, year) update bonus_value = $234234; (Or something along these lines, my syntax might be a bit off ) > > >> I don't think I need to use the where clause. Though I imagine it would >> be a useful pattern. >> > > Yes indeed. Sometimes, it's safe to ignore failed inserts rather than > performing some update. Especially when inserting a whole data set, this is > a useful pattern, which is also available from the SQL standard MERGE > statement. > > I keep wondering why PostgreSQL didn't just implement MERGE, though. > > >> The two main Upsert + Jooq features I would love to see are: >> >> 1. batch operation working with upserts (which is partially supported by >> generating a list of custom insert statements) >> 2. the ability to specify the list of columns rather the .onKeyConflict >> which doesn't support all of my use cases. >> > > Regarding 2), this is the jOOQ 3.9 API: > > /** > * Add an <code>ON CONFLICT</code> clause to this insert query. > */ > @Support({ POSTGRES_9_5 }) > InsertOnConflictDoUpdateStep<R> onConflict(Field<?>... keys); > > /** > * Add an <code>ON CONFLICT</code> clause to this insert query. > */ > @Support({ POSTGRES_9_5 }) > InsertOnConflictDoUpdateStep<R> onConflict(Collection<? extends > Field<?>> keys); > > No, we don't generate indexes yet as that information hasn't been useful >>> to general jOOQ API usage (yet). >>> >> >> That's fine. I suppose there's not much usability added by having java >> objects generated for them. >> > > Well, there is now that we support DDL to create indexes or to restore a > schema from jOOQ classes :) > There, I've added a feature request so this won't be forgotten (in > particular: ON CONFLICT usage with index names): > https://github.com/jOOQ/jOOQ/issues/5638 > I don't think this (indexes) will be necessary for my use case though if others find that useful it might be worth while. The main limitation that I'm running into is that I couldn't list specific Fields for the onConflict which as you listed above address my blockers. The caveat is that the Fields you're listing have to have a unique constraint or Index setup on the DB. But that's more on the developer to be aware, not sure if Jooq could do anything to validate that at compile time. Thank you for all the work and help on this thread. I'm looking forward to 3.9. > > > Thanks, > Lukas > > -- > 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. > -- 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.
