Обсуждение: Reset sequence to current maximum value of rows
Two tables have a sequence for the PK. Over time I manually entered the PK
numbers not being aware of applying DEFAULT to generate the next number.
I just tried to set one table's PK sequence to the current max(PK) value
using this expression from a stackexchange thread:
SELECT setval('<sequence_name>', <current_max_number>, true); -- next value will be max(PK) + 1
Needing to add a new row to a table for a specific industry table (with 52
rows) I set the PK as DEFAULT in the INSERT INTO expression. To my surprise
and disappointment all 52 rows now have the company_name column as the newly
inserted name. Feh! I need to restore all the correct names for each PK.
There's an alternate expression in that SE thread that I didn't try:
ALTER SEQUENCE <sequence_name> RESTART WITH <next_number>;
I want to avoid this same situation when resetting the second table's PK
sequence number and would like to understand why the SELECT expression
changed all column values rather than adding a new row with its attributes.
And how to I reset sequences to ignore all current values while adding the
next higher value to the end when a new row is INSERTed.
TIA,
Rich
On Thu, Jun 13, 2024 at 1:20 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
Two tables have a sequence for the PK. Over time I manually entered the PK
numbers not being aware of applying DEFAULT to generate the next number.
I just tried to set one table's PK sequence to the current max(PK) value
using this expression from a stackexchange thread:
SELECT setval('<sequence_name>', <current_max_number>, true); -- next value will be max(PK) + 1
Needing to add a new row to a table for a specific industry table (with 52
rows) I set the PK as DEFAULT in the INSERT INTO expression. To my surprise
and disappointment all 52 rows now have the company_name column as the newly
inserted name. Feh! I need to restore all the correct names for each PK.
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.
What does your table definition look like?
On Thu, Jun 13, 2024 at 10:20 AM Rich Shepard <rshepard@appl-ecosys.com> wrote:
Two tables have a sequence for the PK. Over time I manually entered the PK
numbers not being aware of applying DEFAULT to generate the next number.
I just tried to set one table's PK sequence to the current max(PK) value
using this expression from a stackexchange thread:
SELECT setval('<sequence_name>', <current_max_number>, true); -- next value will be max(PK) + 1
Needing to add a new row to a table for a specific industry table (with 52
rows) I set the PK as DEFAULT in the INSERT INTO expression. To my surprise
and disappointment all 52 rows now have the company_name column as the newly
inserted name. Feh! I need to restore all the correct names for each PK.
You need to show your work here. As your PK is a number it cannot have a company name as a value and so this doesn't make sense.
There's an alternate expression in that SE thread that I didn't try:
ALTER SEQUENCE <sequence_name> RESTART WITH <next_number>;
This is identical in action to the setval function call you performed.
I want to avoid this same situation when resetting the second table's PK
sequence number and would like to understand why the SELECT expression
changed all column values
It didn't...
rather than adding a new row with its attributes.
It wouldn't do this either...
And how to I reset sequences to ignore all current values
This doesn't make sense...
while adding the
next higher value to the end when a new row is INSERTed.
If you use the default when inserting the next value in the sequence is used.
David J.
On Thu, 13 Jun 2024, David G. Johnston wrote: > You need to show your work here. As your PK is a number it cannot have a > company name as a value and so this doesn't make sense. David, insert into companies (company_nbr,company_name,industry,status) values (DEFAULT,'new company name','Industry','Opportunity') With DEFAULT as the company_nbr why can't the company_name not have a value? > It didn't... I changed the company_name for all company_nbr using the 'new company name' for all rows with 'Industry' in that column. > And how to I reset sequences to ignore all current values > This doesn't make sense... Then I cannot use the sequence for adding new rows in the table and I must manually enter each PK number? > If you use the default when inserting the next value in the sequence is > used. Yes, that's the theory. It didn't work for me and why it didn't is what I want to understand. Regards, Rich
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
On Thu, Jun 13, 2024 at 2:38 PM 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 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.
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
adrian.klaver@aklaver.com
On Thu, Jun 13, 2024 at 11:24 AM Rich Shepard <rshepard@appl-ecosys.com> wrote:
On Thu, 13 Jun 2024, David G. Johnston wrote:
> You need to show your work here. As your PK is a number it cannot have a
> company name as a value and so this doesn't make sense.
insert into companies (company_nbr,company_name,industry,status) values
(DEFAULT,'new company name','Industry','Opportunity')
With DEFAULT as the company_nbr why can't the company_name not have a value?
Because you specified company_name in the column listing for the things you are inserting values for. So in column position 2 you must have a value than can be inserted into the company_name column. It is utterly immaterial how you specified the value for column position 1.
> If you use the default when inserting the next value in the sequence is
> used.
Yes, that's the theory. It didn't work for me and why it didn't is what I
want to understand.
We can't help you understand if you don't show a complete working example and ask a question in relation to that example. I suggest you start from scratch, this time using scripts, so that your work is recorded and replayable.
David J.
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
On Thu, 13 Jun 2024, David G. Johnston wrote: > Because you specified company_name in the column listing for the things > you are inserting values for. So in column position 2 you must have a > value than can be inserted into the company_name column. It is utterly > immaterial how you specified the value for column position 1. > We can't help you understand if you don't show a complete working example > and ask a question in relation to that example. I suggest you start from > scratch, this time using scripts, so that your work is recorded and > replayable. David, INSERT into companies (company_nbr,company_name,industry,status) VALUES (DEFAULT,'A new company name', 'Manufacturing',DEFAULT); I always write scrips for SQL, R, GRASS, Python, bash. Above is a redacted version of the single name I tried adding to the companies table. Yesterday, before learning to use DEFAULT for the company_nbr PK I entered all rows using company_nbr 2342-2391. This morning, after running the single-line INSERT command company numbers from 2341-2392 all had 'A new company name' as the company_name. HTH, Rich
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
On Thu, Jun 13, 2024 at 12:57 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
INSERT into companies (company_nbr,company_name,industry,status) VALUES
(DEFAULT,'A new company name', 'Manufacturing',DEFAULT);
Yesterday, before learning to use DEFAULT for the company_nbr PK I entered
all rows using company_nbr 2342-2391. This morning, after running the
single-line INSERT command company numbers from 2341-2392 all had 'A new
company name' as the company_name.
There is no way, in the absence of a user trigger, that the above insert command changed pre-existing rows. And if you cannot reproduce the behavior you claim to have seen I will continue to just assume you have faulty memory.
David J.
On Thu, 13 Jun 2024, David G. Johnston wrote: > There is no way, in the absence of a user trigger, that the above insert > command changed pre-existing rows. And if you cannot reproduce the > behavior you claim to have seen I will continue to just assume you have > faulty memory. David, While there might be no way that what happened could happen, did happen. You're welcome to your opinion about my memory yet my eyes saw the results of the select statement. Regardless, I updated all of yesterday's company insertions so they're now all correct (again.) Found another one I missed then and successfully inserted it without specifying the PK field or its DEFAULT value as I learned from Ron's reply. Regards, Rich
> 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.
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
On 6/13/24 12:57, Rich Shepard wrote:
> On Thu, 13 Jun 2024, David G. Johnston wrote:
>
>> Because you specified company_name in the column listing for the things
>> you are inserting values for. So in column position 2 you must have a
>> value than can be inserted into the company_name column. It is utterly
>> immaterial how you specified the value for column position 1.
>
>> We can't help you understand if you don't show a complete working example
>> and ask a question in relation to that example. I suggest you start from
>> scratch, this time using scripts, so that your work is recorded and
>> replayable.
>
> David,
>
> INSERT into companies (company_nbr,company_name,industry,status) VALUES
> (DEFAULT,'A new company name', 'Manufacturing',DEFAULT);
>
> I always write scrips for SQL, R, GRASS, Python, bash. Above is a redacted
> version of the single name I tried adding to the companies table.
>
> Yesterday, before learning to use DEFAULT for the company_nbr PK I entered
> all rows using company_nbr 2342-2391. This morning, after running the
Not with:
Table "public.companies"
[...]
Indexes:
"organizations_pkey" PRIMARY KEY, btree (company_nbr)
That would throw duplicate key errors.
Are you sure that you did not do this on the contacts table as the
company FK back to companies?
> single-line INSERT command company numbers from 2341-2392 all had 'A new
> company name' as the company_name.
>
> HTH,
>
> Rich
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On Thu, 13 Jun 2024, Adrian Klaver wrote:
> Not with:
>
> Table "public.companies"
>
> [...]
> Indexes:
> "organizations_pkey" PRIMARY KEY, btree (company_nbr)
>
> That would throw duplicate key errors.
>
> Are you sure that you did not do this on the contacts table as the company FK
> back to companies?
Adrian,
Yes, I'm sure. Early yesterday I did get duplicate key errors. That's when I
looked on stackexchange to learn how to reset the sequence's max value to
the value of the number of rows in the table. Not only did my attempt to add
a single new company to the companies table change all company names in that
one industry to the new name, but I just discovered that it changed all rows
in that column to the new company name:
company_nbr | company_name
-------------+-------------------------------------------------
1 | Markowitz Herbold PC
2 | Markowitz Herbold PC
3 | Markowitz Herbold PC
4 | Markowitz Herbold PC
5 | Markowitz Herbold PC
6 | Markowitz Herbold PC
7 | Markowitz Herbold PC
8 | Markowitz Herbold PC
9 | Markowitz Herbold PC
10 | Markowitz Herbold PC
11 | Markowitz Herbold PC
12 | Markowitz Herbold PC
13 | Markowitz Herbold PC
14 | Markowitz Herbold PC
15 | Markowitz Herbold PC
16 | Markowitz Herbold PC
17 | Markowitz Herbold PC
18 | Markowitz Herbold PC
19 | Markowitz Herbold PC
20 | Markowitz Herbold PC
22 | Markowitz Herbold PC
23 | Markowitz Herbold PC
--More--
So now I need to extract the companies table data from my 2024-06-10 backup
and use that to update the entire table. Sigh. There are 2101 rows in that
table and I must have forgotten to specify industry for that one new
addition. Not like me to do so, but it's the only explanation I have.
It might be quicker for me to restore the entire database from that backup
and then insert all new table rows since I have saved all the scripts.
Regards,
Rich
On 6/13/24 15:13, Rich Shepard wrote: > On Thu, 13 Jun 2024, Adrian Klaver wrote: > >> Not with: >> >> Table "public.companies" >> >> [...] >> Indexes: >> "organizations_pkey" PRIMARY KEY, btree (company_nbr) >> >> That would throw duplicate key errors. >> >> Are you sure that you did not do this on the contacts table as the >> company FK back to companies? > > Adrian, > > Yes, I'm sure. Early yesterday I did get duplicate key errors. That's > when I > looked on stackexchange to learn how to reset the sequence's max value to > the value of the number of rows in the table. Not only did my attempt to > add > a single new company to the companies table change all company names in > that > one industry to the new name, but I just discovered that it changed all > rows > in that column to the new company name: BEGIN; <Run query> <Check query results> Then either ROLLBACK; COMMIT; depending on the result of check. > > Regards, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Jun 13, 2024 at 3:13 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
Yes, I'm sure. Early yesterday I did get duplicate key errors. That's when I
looked on stackexchange to learn how to reset the sequence's max value to
the value of the number of rows in the table. Not only did my attempt to add
a single new company to the companies table change all company names in that
one industry to the new name, but I just discovered that it changed all rows
in that column to the new company name:
At present the belief there is a bug in PostgreSQL is unsubstantiated.
I suggest you look internally for how an update command that caused the resultant data could have been executed. That is much more plausible, and thus a better use of time, if you want to spend more time on this, than trying to produce the observed behavior again using just insert and setval(...) commands.
David J.
> company_nbr | company_name -------------+------------------------------------------------- > 1 | Markowitz Herbold PC > 2 | Markowitz Herbold PC > 3 | Markowitz Herbold PC > 4 | Markowitz Herbold PC > 5 | Markowitz Herbold PC > 6 | Markowitz Herbold PC > 7 | Markowitz Herbold PC > 8 | Markowitz Herbold PC > 9 | Markowitz Herbold PC > 10 | Markowitz Herbold PC > 11 | Markowitz Herbold PC > 12 | Markowitz Herbold PC > 13 | Markowitz Herbold PC > 14 | Markowitz Herbold PC > 15 | Markowitz Herbold PC > 16 | Markowitz Herbold PC > 17 | Markowitz Herbold PC > 18 | Markowitz Herbold PC > 19 | Markowitz Herbold PC > 20 | Markowitz Herbold PC > 22 | Markowitz Herbold PC > 23 | Markowitz Herbold PC > --More-- Did those rows contain these values in some earlier transaction in your data-entry process perhaps? I’m thinking that perhapsyou overwrote them in a later transaction with the correct values for the names, but forgot to commit that transaction? It’s either that, or you did run an UPDATE statement against those rows without specifying a WHERE-clause, as others alreadysuggested as a likely cause. I think we can rule out the possibility of index corruption (a very rare occurrence, usually caused by factors external toPG) for your case. A data-set this limited would most likely result in an execution plan using a sequential scan insteadof an index scan (an EXPLAIN ANALYZE of above select statement would show proof). > It might be quicker for me to restore the entire database from that backup > and then insert all new table rows since I have saved all the scripts. If you end up in the same situation again after doing that, then you know at least it’s repeatable and can analyse how yougot there. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.