agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: Dave Bolt <dave@davebolt.co.uk>
To: pgsql-admin@lists.postgresql.org
Cc: 'Ashkar Dev' <ashkardev@gmail.com>
Subject: RE: duplicate key value violates unique constraint
Date: Sat, 7 Mar 2020 19:54:20 -0000
Message-ID: <03de01d5f4ba$31856da0$949048e0$@co.uk> (raw)
In-Reply-To: <CAHaowgUOCnKehQERBF0qN5gVnghbvr8AefdHhg3X9Aiu0vCaEw@mail.gmail.com>
References: <CAHaowgV2rKK3L30DcjbEy8-L+Pz5qyv8inpsr=y_=qxuXULzLw@mail.gmail.com>
<CAHaowgUOCnKehQERBF0qN5gVnghbvr8AefdHhg3X9Aiu0vCaEw@mail.gmail.com>
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.
view thread (13+ messages) latest in thread
Message-ID: <03de01d5f4ba$31856da0$949048e0$@co.uk>
Permalink: ../03de01d5f4ba$31856da0$949048e0$@co.uk/
Also on: postgresql.org/message-id/03de01d5f4ba$31856da0$949048e0$@co.uk
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: dave@davebolt.co.uk, pgsql-admin@lists.postgresql.org, ashkardev@gmail.com
Subject: RE: duplicate key value violates unique constraint
In-Reply-To: <03de01d5f4ba$31856da0$949048e0$@co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox