MIME-Version: 1.0
References: <387fa6c2-5de-f24-d522-46282597703@appl-ecosys.com>
 <CANzqJaAavydD4XMgkXRUQ_=aAFOGBMVBCKfeB0PnhrZrAWbgng@mail.gmail.com> <c141507f-a21-65dd-55fa-bd9bad7b9fe4@appl-ecosys.com>
In-Reply-To: <c141507f-a21-65dd-55fa-bd9bad7b9fe4@appl-ecosys.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 13 Jun 2024 14:50:43 -0400
Message-ID: <CANzqJaB0OeAchVgnTznmPTbo9Y-H9r9g7S=TqJb25eX_KodcRA@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="00000000000094e4f6061ac9faf9"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaB0OeAchVgnTznmPTbo9Y-H9r9g7S%3DTqJb25eX_KodcRA%40mail.gmail.com>
Precedence: bulk

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

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

> On Thu, 13 Jun 2024, Ron Johnson wrote:
>
> > 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.
>
> Ron,
>
> I had manually set the PKs (column: company_nbr) which has a sequence
> defined for it when I added about 50 rows to the table yesterday.
>
> Now that I'm aware of the DEFAULT option when inserting new rows I tried
> to reset the sequence maximum number to max(company_nbr); the highest
> number
> for the rows inserted yesterday. That's when I tried resetting the curren=
t
> sequence number with the expectation that new rows would be numbered
> sequentially higher than that value.
>
> Today I saw that I had missed one new company and entered it using DEFAUL=
T
> for the company_nbr PK.


No need to do that.  Just write:
INSERT INTO public.companies (company_name, , industry, status)
    VALUES ('Berkshire Hathaway', 'Conglomerate', 'Mumble');

The next value of companies_org_nbr_seq will automatically be taken and
inserted  into the table.

When I looked at that table every company_name that
> I had added yesterday was changed to the one inserted today.
>

You'll have to show us what you did.

--00000000000094e4f6061ac9faf9
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 2:38=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">On Thu, 13 Jun 2024, Ron Johnson wrote:=
<br>
<br>
&gt; If the table has a primary key, then the command *should* have failed =
with<br>
&gt; a duplicate key error as soon as the first dupe was discovered.<br>
<br>
Ron,<br>
<br>
I had manually set the PKs (column: company_nbr) which has a sequence<br>
defined for it when I added about 50 rows to the table yesterday.<br>
<br>
Now that I&#39;m aware of the DEFAULT option when inserting new rows I trie=
d<br>
to reset the sequence maximum number to max(company_nbr); the highest numbe=
r<br>
for the rows inserted yesterday. That&#39;s when I tried resetting the curr=
ent<br>
sequence number with the expectation that new rows would be numbered<br>
sequentially higher than that value.<br>
<br>
Today I saw that I had missed one new company and entered it using DEFAULT<=
br>
for the company_nbr PK.</blockquote><div><br></div><div>No need to do that.=
=C2=A0 Just write:</div><div><font face=3D"monospace">INSERT INTO public.co=
mpanies (company_name, , industry, status)</font></div><div><font face=3D"m=
onospace">=C2=A0 =C2=A0 VALUES (&#39;Berkshire Hathaway&#39;, &#39;Conglome=
rate&#39;, &#39;Mumble&#39;);</font></div><div>=C2=A0</div><div>The next va=
lue of companies_org_nbr_seq will automatically be taken and inserted=C2=A0=
 into the table.</div><div><br></div><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin=
g-left:1ex"> When I looked at that table every company_name that<br>
I had added yesterday was changed to the one inserted today.<br></blockquot=
e><div><br></div><div>You&#39;ll have to show us what you did.=C2=A0</div><=
div><br></div></div></div>

--00000000000094e4f6061ac9faf9--