From: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Reset sequence to current maximum value of rows |
Date: | 2024-06-13 17:20:22 |
Message-ID: | 387fa6c2-5de-f24-d522-46282597703@appl-ecosys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Johnson | 2024-06-13 17:26:12 | Re: Reset sequence to current maximum value of rows |
Previous Message | Justin | 2024-06-13 15:37:20 | Re: Questions on logical replication |