public inbox for [email protected]  

help / color / mirror / Atom feed

From: Rich Shepard <[email protected]>
To: [email protected]
Subject: Reset sequence to current maximum value of rows
Date: Thu, 13 Jun 2024 10:20:22 -0700 (PDT)
Message-ID: <[email protected]> (raw)

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

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]
  Subject: Re: Reset sequence to current maximum value of rows
  In-Reply-To: <[email protected]>

* 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