Date: Thu, 13 Jun 2024 11:38:43 -0700 (PDT)
From: Rich Shepard <rshepard@appl-ecosys.com>
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: Reset sequence to current maximum value of rows
In-Reply-To: <CANzqJaAavydD4XMgkXRUQ_=aAFOGBMVBCKfeB0PnhrZrAWbgng@mail.gmail.com>
Message-ID: <c141507f-a21-65dd-55fa-bd9bad7b9fe4@appl-ecosys.com>
References: <387fa6c2-5de-f24-d522-46282597703@appl-ecosys.com> <CANzqJaAavydD4XMgkXRUQ_=aAFOGBMVBCKfeB0PnhrZrAWbgng@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
Archived-At: <https://www.postgresql.org/message-id/c141507f-a21-65dd-55fa-bd9bad7b9fe4%40appl-ecosys.com>
Precedence: bulk

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