duplicate key value violates unique constraint

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 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?

and if the id reaches the limit and maybe there is some ids that are not
used.

I have two immediate questions on this.

1) Do you Need to make sure there are no gaps in the sequence of id values?

2) Are you ever going to use 9223372036854775807 id values, even with the deletions?

If you want to re-use the id of a deleted row, and it is not going to cause problems elsewhere in your database, you could always have a deleted column in each row instead of actually removing from the table. When you want to insert next, you would just look for the first row where deleted is true and replace it.

Not entirely perfect, but would probably do the job.

From: Ashkar Dev [mailto:ashkardev@gmail.com]
Sent: 07 March 2020 19:35
To: pgsql-admin@lists.postgresql.org
Subject: Fwd: duplicate key value violates unique constraint

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 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?

and if the id reaches the limit and maybe there is some ids that are not used.

On Sat, Mar 7, 2020 at 12:35 PM Ashkar Dev <ashkardev@gmail.com> wrote:

Hi all,

how to fix a problem, suppose there is a table with id and username

You should probably provide the definition though in this case it is
self-evident that you are using bigserial.

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?

Alter sequence

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

and if the id reaches the limit and maybe there is some ids that are not
used.

This is highly unlikely.

Is the subject line supposed to relate to this in some way?

You should not worry about any of this in production; and frankly, worrying
about it in development is largely pointless as well.

There is no actual problem here to be fixed.

David J.

Thanks for answers.

On Sat, Mar 7, 2020 at 10:55 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

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 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?

--
Angular momentum makes the world go 'round.