public inbox for [email protected]
help / color / mirror / Atom feedRe: Reset sequence to current maximum value of rows
7+ messages / 4 participants
[nested] [flat]
* Re: Reset sequence to current maximum value of rows
@ 2024-06-13 18:38 Rich Shepard <[email protected]>
2024-06-13 18:50 ` Re: Reset sequence to current maximum value of rows Ron Johnson <[email protected]>
2024-06-13 18:54 ` Re: Reset sequence to current maximum value of rows Adrian Klaver <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Rich Shepard @ 2024-06-13 18:38 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Reset sequence to current maximum value of rows
2024-06-13 18:38 Re: Reset sequence to current maximum value of rows Rich Shepard <[email protected]>
@ 2024-06-13 18:50 ` Ron Johnson <[email protected]>
2024-06-13 20:09 ` Re: Reset sequence to current maximum value of rows Rich Shepard <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2024-06-13 18:50 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
On Thu, Jun 13, 2024 at 2:38 PM Rich Shepard <[email protected]>
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 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.
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Reset sequence to current maximum value of rows
2024-06-13 18:38 Re: Reset sequence to current maximum value of rows Rich Shepard <[email protected]>
2024-06-13 18:50 ` Re: Reset sequence to current maximum value of rows Ron Johnson <[email protected]>
@ 2024-06-13 20:09 ` Rich Shepard <[email protected]>
2024-06-13 20:30 ` Re: Reset sequence to current maximum value of rows Jeremy Smith <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Rich Shepard @ 2024-06-13 20:09 UTC (permalink / raw)
To: [email protected]
On Thu, 13 Jun 2024, Ron Johnson wrote:
> 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.
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Reset sequence to current maximum value of rows
2024-06-13 18:38 Re: Reset sequence to current maximum value of rows Rich Shepard <[email protected]>
2024-06-13 18:50 ` Re: Reset sequence to current maximum value of rows Ron Johnson <[email protected]>
2024-06-13 20:09 ` Re: Reset sequence to current maximum value of rows Rich Shepard <[email protected]>
@ 2024-06-13 20:30 ` Jeremy Smith <[email protected]>
2024-06-13 21:13 ` Re: Reset sequence to current maximum value of rows Rich Shepard <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Jeremy Smith @ 2024-06-13 20:30 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: [email protected]
> 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?
>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Reset sequence to current maximum value of rows
2024-06-13 18:38 Re: Reset sequence to current maximum value of rows Rich Shepard <[email protected]>
2024-06-13 18:50 ` Re: Reset sequence to current maximum value of rows Ron Johnson <[email protected]>
2024-06-13 20:09 ` Re: Reset sequence to current maximum value of rows Rich Shepard <[email protected]>
2024-06-13 20:30 ` Re: Reset sequence to current maximum value of rows Jeremy Smith <[email protected]>
@ 2024-06-13 21:13 ` Rich Shepard <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Rich Shepard @ 2024-06-13 21:13 UTC (permalink / raw)
To: [email protected]
On Thu, 13 Jun 2024, Jeremy Smith wrote:
> 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.
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Reset sequence to current maximum value of rows
2024-06-13 18:38 Re: Reset sequence to current maximum value of rows Rich Shepard <[email protected]>
@ 2024-06-13 18:54 ` Adrian Klaver <[email protected]>
2024-06-13 19:50 ` Re: Reset sequence to current maximum value of rows Rich Shepard <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Adrian Klaver @ 2024-06-13 18:54 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
On 6/13/24 11:38, Rich Shepard wrote:
> On Thu, 13 Jun 2024, Ron Johnson wrote:
>
> 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.
You sure you did not actually do an UPDATE without a WHERE?
>
>> 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
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Reset sequence to current maximum value of rows
2024-06-13 18:38 Re: Reset sequence to current maximum value of rows Rich Shepard <[email protected]>
2024-06-13 18:54 ` Re: Reset sequence to current maximum value of rows Adrian Klaver <[email protected]>
@ 2024-06-13 19:50 ` Rich Shepard <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Rich Shepard @ 2024-06-13 19:50 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
On Thu, 13 Jun 2024, Adrian Klaver wrote:
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-06-13 21:13 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-13 18:38 Re: Reset sequence to current maximum value of rows Rich Shepard <[email protected]>
2024-06-13 18:50 ` Ron Johnson <[email protected]>
2024-06-13 20:09 ` Rich Shepard <[email protected]>
2024-06-13 20:30 ` Jeremy Smith <[email protected]>
2024-06-13 21:13 ` Rich Shepard <[email protected]>
2024-06-13 18:54 ` Adrian Klaver <[email protected]>
2024-06-13 19:50 ` Rich Shepard <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox