Hello,
one of the most frustating things when I started with PostgreSQL was
that IDENTITY columns are based on sequences that are completly
disconnected from the table contents and manually imported data will
lead to errors like 'duplicate key value violates unique constraint
"xyz_pkey"'.
I had to fight this initially with an insert trigger that always updates
the sequences on each insert, or with client side code that updates the
sequence when such an error occurs and then retries the insert.
Even Microsoft Access did a better job at autogenerated primaries keys,
and while I love the elegant design of PostgreSQL in many ways I believe
we can do better here. I would like to implement a fallback solution
that detects such errors and automatically updates the nextvalue of the
sequence when the nextvalue is already used on insert.
I believe this can be implemented without affecting performance
negatively when one just does extra stuff in the error case, so I
wouldn't do table scans when creating the insert initially.
Any reasons why this isn't a good idea to try?
Regards,
Daniel Migowski