3 ms·
It increments the sequence associated with with a "serial" or "bigserial" field—usually used for primary keys. People are often surprised by this because they e
by neallindsay 3y ago
It increments the sequence associated with with a "serial" or "bigserial" field—usually used for primary keys. People are often surprised by this because they expect their primary keys to be sequential and sequences are designed to leave gaps in order to avoid a lot of locking.
- hn_throwaway_99 3y agoThat just seems like a fundamental misunderstanding of sequences to me. They are guaranteed to be increasing but not necessarily sequential.
- richbell 3y agoI think it's more confusion of how `ON CONFLICT DO NOTHING` works. Incrementing sequences is not "doing nothing", even if it's valid and sensible behaviour, which leads to confusion.
- cdogl 3y agoIn fairness to Postgres, “DO NOTHING” is accurate within the SQL domain if you squint. It’s reasonable for queries to have side-effects on the underlying implementation as long as the invariants defined by SQL are met. Surprising, yes, but the alternative would be leaking implementation details in the syntax. On the other hand, it’s so easy to use and rely on autoincrement IDs and to assume they are monotonic and predictable based on naive testing. If we could do it all again I’d fuzz the IDs even during normal operation (occasionally increment by a random extra amount) so that they don’t become such a foot gun for developers.