Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1jAfWo-00008E-Di for pgsql-admin@arkaria.postgresql.org; Sat, 07 Mar 2020 19:54:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1jAfWm-0001jL-J4 for pgsql-admin@arkaria.postgresql.org; Sat, 07 Mar 2020 19:54:28 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1jAfWm-0001j8-2X for pgsql-admin@lists.postgresql.org; Sat, 07 Mar 2020 19:54:28 +0000 Received: from nativespace-chaldene.ns-chaldene.com ([212.53.86.59]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jAfWi-0007iy-Ii for pgsql-admin@lists.postgresql.org; Sat, 07 Mar 2020 19:54:26 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=davebolt.co.uk; s=default; h=Content-Type:MIME-Version:Message-ID:Date: Subject:In-Reply-To:References:Cc:To:From:Sender:Reply-To: Content-Transfer-Encoding:Content-ID:Content-Description:Resent-Date: Resent-From:Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID:List-Id: List-Help:List-Unsubscribe:List-Subscribe:List-Post:List-Owner:List-Archive; bh=oLmknZL7+0rECFEEbY3t1NWdlI3SbqORgG3njWC38OY=; b=BfEe8AkmjjnHCwz9lD820Qwb6 HHEk1uP6T2QSFmT45p80sJIr5+yK58Lsitu2ViXNF1EnCchTxKLXMW8HCbzKro5b7LwaekJU/X0RY WxSZjacAuoTbVe041gQIXeJGUYFV8VOvh0zLFaO/LDuq/PY5Lxcxwr5/CK1E5RUDTdp18=; Received: from davebolt.plus.com ([80.229.145.181]:64647 helo=BELONGADAVEX) by nativespace-chaldene.ns-chaldene.com with esmtpsa (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256) (Exim 4.92) (envelope-from ) id 1jAfWd-000PTP-4I; Sat, 07 Mar 2020 19:54:19 +0000 From: "Dave Bolt" To: Cc: "'Ashkar Dev'" References: In-Reply-To: Subject: RE: duplicate key value violates unique constraint Date: Sat, 7 Mar 2020 19:54:20 -0000 Message-ID: <03de01d5f4ba$31856da0$949048e0$@co.uk> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_03DF_01D5F4BA.31856DA0" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AdX0t6avFC2iL4P1SQeQUUpIj4SBlwAAaT/w Content-Language: en-gb X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - nativespace-chaldene.ns-chaldene.com X-AntiAbuse: Original Domain - lists.postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - davebolt.co.uk X-Get-Message-Sender-Via: nativespace-chaldene.ns-chaldene.com: authenticated_id: dave@davebolt.co.uk X-Authenticated-Sender: nativespace-chaldene.ns-chaldene.com: dave@davebolt.co.uk List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. ------=_NextPart_000_03DF_01D5F4BA.31856DA0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable 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. =20 =20 From: Ashkar Dev [mailto:ashkardev@gmail.com]=20 Sent: 07 March 2020 19:35 To: pgsql-admin@lists.postgresql.org Subject: Fwd: duplicate key value violates unique constraint =20 =20 Hi all, how to fix a problem, suppose there is a table with id and username =20 if I set the id to bigint so the limit is 9223372036854775807=20 if I insert for example 3 rows id username -- -------------- 1 abc 2 def 3 ghi =20 if I delete all rows and insert one another it is like =20 id username -- -------------- 4 jkl =20 =20 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? =20 and if the id reaches the limit and maybe there is some ids that are not = used. ------=_NextPart_000_03DF_01D5F4BA.31856DA0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

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.

------=_NextPart_000_03DF_01D5F4BA.31856DA0--