MIME-Version: 1.0
References: <387fa6c2-5de-f24-d522-46282597703@appl-ecosys.com>
In-Reply-To: <387fa6c2-5de-f24-d522-46282597703@appl-ecosys.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 13 Jun 2024 13:26:12 -0400
Message-ID: <CANzqJaAavydD4XMgkXRUQ_=aAFOGBMVBCKfeB0PnhrZrAWbgng@mail.gmail.com>
Subject: Re: Reset sequence to current maximum value of rows
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000057a06d061ac8cc29"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAavydD4XMgkXRUQ_%3DaAFOGBMVBCKfeB0PnhrZrAWbgng%40mail.gmail.com>
Precedence: bulk

--00000000000057a06d061ac8cc29
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Jun 13, 2024 at 1:20=E2=80=AFPM Rich Shepard <rshepard@appl-ecosys.=
com>
wrote:

> Two tables have a sequence for the PK. Over time I manually entered the P=
K
> numbers not being aware of applying DEFAULT to generate the next number.
>
> I just tried to set one table's PK sequence to the current max(PK) value
> using this expression from a stackexchange thread:
> SELECT setval('<sequence_name>', <current_max_number>, true);  -- next
> value will be max(PK) + 1
>
> Needing to add a new row to a table for a specific industry table (with 5=
2
> rows) I set the PK as DEFAULT in the INSERT INTO expression. To my surpri=
se
> and disappointment all 52 rows now have the company_name column as the
> newly
> inserted name. Feh! I need to restore all the correct names for each PK.
>

If the table has a primary key, then the command *should* have failed with
a duplicate key error as soon as the first dupe was discovered.

What does your table definition look like?

--00000000000057a06d061ac8cc29
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Jun 13, 2024 at 1:20=E2=80=AFPM R=
ich Shepard &lt;<a href=3D"mailto:rshepard@appl-ecosys.com">rshepard@appl-e=
cosys.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex">Two tables have a sequence for the PK. =
Over time I manually entered the PK<br>
numbers not being aware of applying DEFAULT to generate the next number.<br=
>
<br>
I just tried to set one table&#39;s PK sequence to the current max(PK) valu=
e<br>
using this expression from a stackexchange thread:<br>
SELECT setval(&#39;&lt;sequence_name&gt;&#39;, &lt;current_max_number&gt;, =
true);=C2=A0 -- next value will be max(PK) + 1<br>
<br>
Needing to add a new row to a table for a specific industry table (with 52<=
br>
rows) I set the PK as DEFAULT in the INSERT INTO expression. To my surprise=
<br>
and disappointment all 52 rows now have the company_name column as the newl=
y<br>
inserted name. Feh! I need to restore all the correct names for each PK.<br=
>
</blockquote><div><br></div><div>If the table has a primary key, then the c=
ommand <b><i>should</i></b>=C2=A0have failed with a duplicate key error as =
soon as the first dupe was discovered.</div><div><br></div><div>What does y=
our table definition look like?</div><div><br></div></div></div>

--00000000000057a06d061ac8cc29--