Subject: Re: Reset sequence to current maximum value of rows

From: "David G. Johnston" <[email-masked]>
Date: Thu, 13 Jun 2024 10:28:10 -0700

On Thu, Jun 13, 2024 at 10:20 AM Rich Shepard <[email-masked]>
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.

---

From: Rich Shepard <[email-masked]>
Date: Thu, 13 Jun 2024 11:24:37 -0700 (PDT)

> 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.

> 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

Yes, that's the theory. It didn't work for me and why it didn't is what I
want to understand.

Regards,

Rich

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 13 Jun 2024 12:35:01 -0700

> With DEFAULT as the company_nbr why can't the company_name not have a

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.

> Yes, that's the theory. It didn't work for me and why it didn't is what I
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.

---

From: Rich Shepard <[email-masked]>
Date: Thu, 13 Jun 2024 12:57:19 -0700 (PDT)

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

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

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 13 Jun 2024 13:14:56 -0700

> 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.

---

From: Rich Shepard <[email-masked]>
Date: Thu, 13 Jun 2024 13:22:09 -0700 (PDT)

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

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 13 Jun 2024 15:01:23 -0700

> 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?

-- 
Adrian Klaver
[email-masked]

---

From: Rich Shepard <[email-masked]>
Date: Thu, 13 Jun 2024 15:13:37 -0700 (PDT)

On Thu, 13 Jun 2024, Adrian Klaver wrote:

> 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

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 13 Jun 2024 15:24:51 -0700

> in that column to the new company name:

BEGIN;

<Run query>

<Check query results>

Then either

ROLLBACK;
COMMIT;

depending on the result of check.

---

From: Alban Hertroys <[email-masked]>
Date: Fri, 14 Jun 2024 09:08:51 +0200

> 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

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).

> 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.