pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: Ashkar Dev <ashkardev@gmail.com>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: duplicate key value violates unique constraint
Date: Sat, 7 Mar 2020 12:28:05 -0800
Message-ID: <b6ae119e-3d98-687d-d7c4-fa874bc2852a@aklaver.com> (raw)
In-Reply-To: <CAHaowgV2rKK3L30DcjbEy8-L+Pz5qyv8inpsr=y_=qxuXULzLw@mail.gmail.com>
References: <CAHaowgV2rKK3L30DcjbEy8-L+Pz5qyv8inpsr=y_=qxuXULzLw@mail.gmail.com>
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
view thread (13+ messages) latest in thread
Message-ID: <b6ae119e-3d98-687d-d7c4-fa874bc2852a@aklaver.com>
Permalink: ../b6ae119e-3d98-687d-d7c4-fa874bc2852a@aklaver.com/
Also on: postgresql.org/message-id/b6ae119e-3d98-687d-d7c4-fa874bc2852a@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: adrian.klaver@aklaver.com, ashkardev@gmail.com, pgsql-general@lists.postgresql.org
Subject: Re: duplicate key value violates unique constraint
In-Reply-To: <b6ae119e-3d98-687d-d7c4-fa874bc2852a@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox