public inbox for [email protected]
help / color / mirror / Atom feedFrom: David G. Johnston <[email protected]>
To: Rich Shepard <[email protected]>
Cc: [email protected]
Subject: Re: Reset sequence to current maximum value of rows
Date: Thu, 13 Jun 2024 10:28:10 -0700
Message-ID: <CAKFQuwba=vb16vR47W0oo3SirkJ+UqLEg5j9-fqe+vm=0Q7_qw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[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.
view thread (10+ 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], [email protected]
Subject: Re: Reset sequence to current maximum value of rows
In-Reply-To: <CAKFQuwba=vb16vR47W0oo3SirkJ+UqLEg5j9-fqe+vm=0Q7_qw@mail.gmail.com>
* 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