public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rich Shepard <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Reset sequence to current maximum value of rows
Date: Thu, 13 Jun 2024 11:38:43 -0700 (PDT)
Message-ID: <[email protected]> (raw)
In-Reply-To: <CANzqJaAavydD4XMgkXRUQ_=aAFOGBMVBCKfeB0PnhrZrAWbgng@mail.gmail.com>
References: <[email protected]>
<CANzqJaAavydD4XMgkXRUQ_=aAFOGBMVBCKfeB0PnhrZrAWbgng@mail.gmail.com>
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
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Reset sequence to current maximum value of rows
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox