Обсуждение: duplicate key value violates unique constraint
Hi all,
how to fix a problem, suppose there is a table with id and username
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 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?
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.klaver@aklaver.com
Asking the same question on multiple lists is strongly frowned upon.
On 3/7/20 1:29 PM, Ashkar Dev wrote:
Hi all,
how to fix a problem, suppose there is a table with id and usernameif I set the id to bigint so the limit is 9223372036854775807if I insert for example 3 rowsid username-- --------------1 abc2 def3 ghiif I delete all rows and insert one another it is likeid username-- --------------4 jklSo 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?
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.