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@xxxxxxxxxxx
