public inbox for [email protected]help / color / mirror / Atom feed
Re: Reset sequence to current maximum value of rows 10+ messages / 4 participants [nested] [flat]
* Re: Reset sequence to current maximum value of rows @ 2024-06-13 17:28 David G. Johnston <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: David G. Johnston @ 2024-06-13 17:28 UTC (permalink / raw) To: Rich Shepard <[email protected]>; +Cc: [email protected] On Thu, Jun 13, 2024 at 10:20 AM Rich Shepard <[email protected]> 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. ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reset sequence to current maximum value of rows @ 2024-06-13 18:24 Rich Shepard <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Rich Shepard @ 2024-06-13 18:24 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reset sequence to current maximum value of rows @ 2024-06-13 19:35 David G. Johnston <[email protected]> parent: Rich Shepard <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: David G. Johnston @ 2024-06-13 19:35 UTC (permalink / raw) To: Rich Shepard <[email protected]>; +Cc: [email protected] On Thu, Jun 13, 2024 at 11:24 AM Rich Shepard <[email protected]> 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. ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reset sequence to current maximum value of rows @ 2024-06-13 19:57 Rich Shepard <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 2 replies; 10+ messages in thread From: Rich Shepard @ 2024-06-13 19:57 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reset sequence to current maximum value of rows @ 2024-06-13 20:14 David G. Johnston <[email protected]> parent: Rich Shepard <[email protected]> 1 sibling, 1 reply; 10+ messages in thread From: David G. Johnston @ 2024-06-13 20:14 UTC (permalink / raw) To: Rich Shepard <[email protected]>; +Cc: [email protected] On Thu, Jun 13, 2024 at 12:57 PM Rich Shepard <[email protected]> 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. ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reset sequence to current maximum value of rows [RESOLVED] @ 2024-06-13 20:22 Rich Shepard <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Rich Shepard @ 2024-06-13 20:22 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reset sequence to current maximum value of rows @ 2024-06-13 22:01 Adrian Klaver <[email protected]> parent: Rich Shepard <[email protected]> 1 sibling, 1 reply; 10+ messages in thread From: Adrian Klaver @ 2024-06-13 22:01 UTC (permalink / raw) To: Rich Shepard <[email protected]>; [email protected] 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 [email protected] ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reset sequence to current maximum value of rows @ 2024-06-13 22:13 Rich Shepard <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 2 replies; 10+ messages in thread From: Rich Shepard @ 2024-06-13 22:13 UTC (permalink / raw) To: [email protected] 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 ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reset sequence to current maximum value of rows @ 2024-06-13 22:24 Adrian Klaver <[email protected]> parent: Rich Shepard <[email protected]> 1 sibling, 0 replies; 10+ messages in thread From: Adrian Klaver @ 2024-06-13 22:24 UTC (permalink / raw) To: Rich Shepard <[email protected]>; [email protected] 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 [email protected] ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reset sequence to current maximum value of rows @ 2024-06-14 07:08 Alban Hertroys <[email protected]> parent: Rich Shepard <[email protected]> 1 sibling, 0 replies; 10+ messages in thread From: Alban Hertroys @ 2024-06-14 07:08 UTC (permalink / raw) To: Rich Shepard <[email protected]>; +Cc: pgsql-general <[email protected]> > 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 perhaps you 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 already suggested as a likely cause. I think we can rule out the possibility of index corruption (a very rare occurrence, usually caused by factors external to PG) for your case. A data-set this limited would most likely result in an execution plan using a sequential scan instead of 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 you got there. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. ^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2024-06-14 07:08 UTC | newest] Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-06-13 17:28 Re: Reset sequence to current maximum value of rows David G. Johnston <[email protected]> 2024-06-13 18:24 ` Rich Shepard <[email protected]> 2024-06-13 19:35 ` David G. Johnston <[email protected]> 2024-06-13 19:57 ` Rich Shepard <[email protected]> 2024-06-13 20:14 ` David G. Johnston <[email protected]> 2024-06-13 20:22 ` Re: Reset sequence to current maximum value of rows [RESOLVED] Rich Shepard <[email protected]> 2024-06-13 22:01 ` Adrian Klaver <[email protected]> 2024-06-13 22:13 ` Rich Shepard <[email protected]> 2024-06-13 22:24 ` Adrian Klaver <[email protected]> 2024-06-14 07:08 ` Alban Hertroys <[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