public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ron Johnson <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Reset sequence to current maximum value of rows
Date: Thu, 13 Jun 2024 13:26:12 -0400
Message-ID: <CANzqJaAavydD4XMgkXRUQ_=aAFOGBMVBCKfeB0PnhrZrAWbgng@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On Thu, Jun 13, 2024 at 1:20 PM 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.
>

If the table has a primary key, then the command *should* have failed with
a duplicate key error as soon as the first dupe was discovered.

What does your table definition look like?

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: <CANzqJaAavydD4XMgkXRUQ_=aAFOGBMVBCKfeB0PnhrZrAWbgng@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