On Thu, Jun 13, 2024 at 10:20 AM Rich Shepard <rshepard@xxxxxxxxxxxxxxx> 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.
