Re: duplicate key value violates unique constraint

On 3/7/20 11:29 AM, Ashkar Dev wrote:
> Hi all,
>
> how to fix a problem, suppose there is a table with id and username
>
> if I set the id to bigint so the limit is 9223372036854775807
> if I insert for example 3 rows
> id    username
> --    --------------
> 1     abc
> 2     def
> 3     ghi
>
> if I delete all rows and insert one another it is like
>
> id    username
> --    --------------
> 4     jkl

So I am assuming id is of type bigserial or something that has a
sequence behind it?

>
>
> So it doesn't start again from non-available id 1, so what is needed to
> do to make the new inserts go into non-available id numbers?

If you are sequences then they do not go backwards:

https://www.postgresql.org/docs/12/sql-createsequence.html

"Because nextval and setval calls are never rolled back, sequence
objects cannot be used if “gapless” assignment of sequence numbers is
needed. It is possible to build gapless assignment by using exclusive
locking of a table containing a counter; but this solution is much more
expensive than sequence objects, especially if many transactions need
sequence numbers concurrently."

If you want that to happen you will have to roll your own implementation.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com