Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sHoFp-00Avr6-EM for pgsql-general@arkaria.postgresql.org; Thu, 13 Jun 2024 17:28:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sHoFn-004Wup-8B for pgsql-general@arkaria.postgresql.org; Thu, 13 Jun 2024 17:28:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sHoFm-004WuQ-Sq for pgsql-general@lists.postgresql.org; Thu, 13 Jun 2024 17:28:51 +0000 Received: from mail-ot1-x32c.google.com ([2607:f8b0:4864:20::32c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sHoFl-001V8j-I6 for pgsql-general@lists.postgresql.org; Thu, 13 Jun 2024 17:28:51 +0000 Received: by mail-ot1-x32c.google.com with SMTP id 46e09a7af769-6f97b836c42so709275a34.1 for ; Thu, 13 Jun 2024 10:28:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1718299727; x=1718904527; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=8YJAO537ijgvkME3ZccfwHJGdfuZTPK1TF9Yeto7evc=; b=MDBSjYiPIOndBd2azl81qrijhLEUjTADN8e+p3rblmFdt7LpLwUePGvNrG6xUne/EG DwcnC8eoyVs9t5IvIhVV0lzoKsWtT0RHVxGqnW3U0yGe7hUmsGPgROMNL/oNauhfeV03 pKvVyvRZzYebJvX+y788vyZP3pJT6MKgJavzks8l6/sJPjIhYJlI536XcloCuyBu0VU5 qWaffnBRZDO4Gy2eYnKsOql7hZgRyVEKx6LSADhyWEzkwL4Zb5xIHM6NwJFx7BYEUCyB BqPY8xuYHnplOi98jhhgI5SyLPesKkNgEz5IiYsE+GQmIaFPwAElc4Rm56vgQXT80lL5 c4qg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1718299727; x=1718904527; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=8YJAO537ijgvkME3ZccfwHJGdfuZTPK1TF9Yeto7evc=; b=Cwcd22RsyrzlqFR5hpepQ4WGLaTnPLAz9A+TpsPmAG2FBygZo6NBfgGoYy44QovTg/ caVJtPNC8Ix12S0k1kO7s5gLx2jBORgDBMykzHu48AkFlQUVZGL0u9ck8w3UPN4jrSeH Fk/pQ2/f2p8euvpieHk8sHhZxNwDhSxtNTKmgXhTqMu+hzOD39Su+dOw4MVxixosZ5sG T+YwehoZ7rKxL1CEeL8pZM3gFjrdZpdu2xypktsKunKF6oz5UmnrXh1bY0NuRXi+B0kS VQpWXsgzkegc6lguPxkAEr/2KuPE+XFCCASaP6RNZzr6mL3Tb3QVxpZz0gjy+Djhs25q VIRA== X-Gm-Message-State: AOJu0Yyfwm9JovxOxLIE1q+lAOqcG+1KwvD+obcP57+gLaWbMhm065il pzak3XLhq4eL8LvL8cw3daKsl6o1Qn7IlFnns9wcIzbnYjyZZHTdBBKaR3vC8kEae5XZ6jzYP7j mgwlhyd5+cADAgpBEkJ9jHYZCzps2dA== X-Google-Smtp-Source: AGHT+IH9z8x1Q4yRFM8Hn6l3HHiQYMy3hUM5qKB5ZurYQ29vOVCPfFQGh+MMdT35PTYZ9VuJ6zRPddRntP8YPs+EjgM= X-Received: by 2002:a05:6871:5b13:b0:254:affe:5a05 with SMTP id 586e51a60fabf-25842910c48mr302711fac.21.1718299727342; Thu, 13 Jun 2024 10:28:47 -0700 (PDT) MIME-Version: 1.0 References: <387fa6c2-5de-f24-d522-46282597703@appl-ecosys.com> In-Reply-To: <387fa6c2-5de-f24-d522-46282597703@appl-ecosys.com> From: "David G. Johnston" Date: Thu, 13 Jun 2024 10:28:10 -0700 Message-ID: Subject: Re: Reset sequence to current maximum value of rows To: Rich Shepard Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000ddb48c061ac8d483" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ddb48c061ac8d483 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Jun 13, 2024 at 10:20=E2=80=AFAM Rich Shepard wrote: > Two tables have a sequence for the PK. Over time I manually entered the P= K > 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('', , true); -- next > value will be max(PK) + 1 > > Needing to add a new row to a table for a specific industry table (with 5= 2 > rows) I set the PK as DEFAULT in the INSERT INTO expression. To my surpri= se > 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 RESTART WITH ; > 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. --000000000000ddb48c061ac8d483 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Jun 13, 2024 at 10:20=E2=80=AFAM Rich Shepard <= rshepard@appl-ecosys.com>= ; wrote:
Two tables have a sequence for the PK. Ove= r 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) valu= e
using this expression from a stackexchange thread:
SELECT setval('<sequence_name>', <current_max_number>, = true);=C2=A0 -- next value will be max(PK) + 1

Needing to add a new row to a table for a specific industry table (with 52<= br> 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 newl= y
inserted name. Feh! I need to restore all the correct names for each PK.

You need to show your work here.=C2=A0 As y= our PK is a number it cannot have a company name as a value and so this doe= sn'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>;
<= /blockquote>

This is identical in action to the setval fun= ction call you performed.


I want to avoid this same situation when resetting the second table's P= K
sequence number and would like to understand why the SELECT expression
changed all column values

It didn't.= ..
=C2=A0
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.
--000000000000ddb48c061ac8d483--