Subject: Re: duplicate key value violates unique constraint
To: Ashkar Dev <ashkardev@gmail.com>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: 
 <CAHaowgV2rKK3L30DcjbEy8-L+Pz5qyv8inpsr=y_=qxuXULzLw@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <b6ae119e-3d98-687d-d7c4-fa874bc2852a@aklaver.com>
Date: Sat, 7 Mar 2020 12:28:05 -0800
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.5.0
MIME-Version: 1.0
In-Reply-To: 
 <CAHaowgV2rKK3L30DcjbEy8-L+Pz5qyv8inpsr=y_=qxuXULzLw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

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