Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPiF9-0006ar-DB for pgsql-general@arkaria.postgresql.org; Wed, 08 Feb 2023 11:04:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pPiF8-000531-8y for pgsql-general@arkaria.postgresql.org; Wed, 08 Feb 2023 11:04:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPiF7-00052q-To for pgsql-general@lists.postgresql.org; Wed, 08 Feb 2023 11:04:01 +0000 Received: from wnew2-smtp.messagingengine.com ([64.147.123.27]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pPiF3-0005w1-Un for pgsql-general@lists.postgresql.org; Wed, 08 Feb 2023 11:04:01 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailnew.west.internal (Postfix) with ESMTP id 0097C2B066ED; Wed, 8 Feb 2023 06:03:53 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute3.internal (MEProxy); Wed, 08 Feb 2023 06:03:54 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :date:date:feedback-id:feedback-id:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:sender :subject:subject:to:to:x-me-proxy:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm1; t=1675854233; x=1675861433; bh=p x7pvPK7aKvwqXGKCy7wZ1LzTPkFFB6yaWaQs8QOu4E=; b=Vhrq+TOJXADdfy6Qb pFML9MLwsp7JHZrScZgrK8wih6PqODc9ZVvMXgnpn/GUXp0kuthat9NpbpK+pPMd zD1nwI1aN1Vt/RtZGKPt1v2PM/zLDXv2j44pLZ1e4NhyI+nzvhg1Z0Ay0a8ViTq1 YJLYxTqnRJoEGYSzZcyCnQ3K7V7mV9B23q1WOnSRkEIepT03+UJ8U92yHo3XBl4e KvrS6DEVHWZOLw+hmUpjW4+Kkuny2b/L5ufuXIobKjlFc5UCr0MgQkBVAjSnL9fe I2AAZ7wRH6Izofcl9vxaWoPT/JvJSpQnTckokrTjXOo1nq6Xv9FcyZ3VNQDcRiZd M3pwg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrudehtddgvddvucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvfhfhjggtgfesthejredttdefjeenucfhrhhomheprfgvthgv rhcugfhishgvnhhtrhgruhhtuceophgvthgvrhdrvghishgvnhhtrhgruhhtsegvnhhtvg hrphhrihhsvggusgdrtghomheqnecuggftrfgrthhtvghrnheptddtheeivefhfeegjeeu keeiiedukedvgffgieeikeejvdehhfegudelheevtdehnecuffhomhgrihhnpedvnhguqh hurggurhgrnhhtrdgtohhmnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehm rghilhhfrhhomhepphgvthgvrhdrvghishgvnhhtrhgruhhtsegvnhhtvghrphhrihhsvg gusgdrtghomh X-ME-Proxy: Feedback-ID: i131946ab:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 8 Feb 2023 06:03:52 -0500 (EST) Message-ID: <87c8d1ad-e220-11db-8e3e-d66226feda74@enterprisedb.com> Date: Wed, 8 Feb 2023 12:03:48 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0) Gecko/20100101 Thunderbird/102.6.1 Subject: Re: Switching identity column to serial Content-Language: en-US To: Erik Wienhold , Ron , pgsql-general@lists.postgresql.org References: <1828022907.15226.1675472079824@office.mailbox.org> <0400e92f-0d77-4276-be50-6e16321f4ae0@gmail.com> <2023065411.42133.1675544152872@office.mailbox.org> From: Peter Eisentraut In-Reply-To: <2023065411.42133.1675544152872@office.mailbox.org> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 04.02.23 21:55, Erik Wienhold wrote: >> Why doesn't this work? >> BEGIN; >> DROP SEQUENCE t_id; [This won't work, you need to use ALTER TABLE / DROP IDENTITY.] >> CREATE SEQUENCE new_t_id_seq AS INTEGER OWNED BY t.id; >> ALTER SEQUENCE new_t_id_seq OWNER TO new_owner; >> SELECT setval('new_t_id', (SELECT MAX(id) FROM t)); >> SELECT nextval('new_t_id'); >> COMMIT; > This should work but I want to preserve the existing sequence instead of > re-creating it with the same properties. That's why I was looking for a > shortcut (also code golfing and sheer curiosity). This is possible in principle, by implementing the inverse of the "Upgrading" recipe from . But if you don't want to figure that out, I think dropping and recreating the sequences as suggested here seems the best solution.