Subject: Re: Reset sequence to current maximum value of rows

From: Rich Shepard <[email-masked]>
Date: Thu, 13 Jun 2024 11:38:43 -0700 (PDT)

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 current
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 DEFAULT
for the company_nbr PK. When I looked at that table every company_name that
I had added yesterday was changed to the one inserted today.

> What does your table definition look like?

                                          Table "public.companies"
     Column    |         Type          | Collation | Nullable |                  Default

--------------+-----------------------+-----------+----------+--------------------------------------
  company_nbr  | integer               |           | not null | nextval('companies_org_nbr_seq'::regclass)
  company_name | character varying(64) |           | not null | '??'::character varying
  url          | character varying(64) |           |          |
  email        | character varying(64) |           |          |
  industry     | character varying(24) |           | not null | 'Other'::character varying
  status       | character varying(20) |           | not null | 'Opportunity'::character varying
  comment      | text                  |           |          |
  ea_nbr       | integer               |           |          | 0
  ea_amt       | numeric(10,2)         |           |          | 0.00
Indexes:
     "organizations_pkey" PRIMARY KEY, btree (company_nbr)
Foreign-key constraints:
     "organizations_industry_fkey" FOREIGN KEY (industry) REFERENCES industrytypes(ind_name) ON UPDAT
E CASCADE ON DELETE RESTRICT
     "organizations_status_fkey" FOREIGN KEY (status) REFERENCES statustypes(stat_name) ON UPDATE CAS
CADE ON DELETE RESTRICT
Referenced by:
     TABLE "locations" CONSTRAINT "locations_org_nbr_fkey" FOREIGN KEY
(company_nbr) REFERENCES companies(company_nbr) ON UPDATE CASCADE ON DELETE
RESTRICT
     TABLE "people" CONSTRAINT "people_org_nbr_fkey" FOREIGN KEY
(company_nbr) REFERENCES companies(c ompany_nbr) ON UPDATE CASCADE ON DELETE
RESTRICT

Rich

---

From: Ron Johnson <[email-masked]>
Date: Thu, 13 Jun 2024 14:50:43 -0400

On Thu, Jun 13, 2024 at 2:38 PM Rich Shepard <[email-masked]>
wrote:

> 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.

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 13 Jun 2024 11:54:18 -0700

> I had added yesterday was changed to the one inserted today.

You sure you did not actually do an UPDATE without a WHERE?

>   company_nbr  | integer               |           | not null | 
> nextval('companies_org_nbr_seq'::regclass)

-- 
Adrian Klaver
[email-masked]

---

From: Rich Shepard <[email-masked]>
Date: Thu, 13 Jun 2024 12:50:07 -0700 (PDT)

> You sure you did not actually do an UPDATE without a WHERE?

Adrian,

Yep. There was no row to update as I was adding a new company.

Regards,

Rich

---

From: Rich Shepard <[email-masked]>
Date: Thu, 13 Jun 2024 13:09:51 -0700 (PDT)

Ron,

Aha! So it's likely that by listing the PK column name in the list of
columns to be inserted was what caused the problem? No need to specify
DEFAULT for it?

I'm writing a script to enter all contact within these companies. I'll leave
out 'person_nbr' in the list of columns and DEFAULT at the beginning of the
VALUES () section.

I didn't pick this up in my readings.

Thank you,

Rich

---

From: Jeremy Smith <[email-masked]>
Date: Thu, 13 Jun 2024 16:30:32 -0400

> columns to be inserted was what caused the problem? No need to specify
There's no need to specify the column if it has a default value, but
specifying it did not cause the issue that you saw.

---

From: Rich Shepard <[email-masked]>
Date: Thu, 13 Jun 2024 14:13:26 -0700 (PDT)

> specifying it did not cause the issue that you saw.

Jeremy,

I did not know this. While the reason for the issue shall remain unknown, it
did happen and my update script restored order to the table.

Thanks,

Rich