< 1. The id column is last, so quite possibly added later (instead of

< the original PK which was dropped?)

<
That seems likely, and probably the 2^nd sequence was added = in by someone who didn't know (or forgot) about the first one.

<

< 2. The two sequences are just dumped -- which causes an error when imp= orting.
<
I'd be curious to know if simple editing out the 2^nd "= ALTER TABLE...ADD GENERATED.." statement would allow a restore of the = database to succeed. pg_restore dorsn't work with plaintext files, you have= to cat them into psql or use the '-f' switch.

< A third thing that is interesting is that I can drop the table just

< fine -- and both sequences get dropped along with it.

< The table seems to be relatively small -- and has no foreign keys --

< so I think the solution will be to recreate the table (create table as=

< select), drop the original table and finally rename the new table the<= /div>

< same as the old one.

<

That's probably the quickest way to fix it, though if you are "create = table as select.."-ing from the old table you might get the two sequen= ces again. I've never used "create table as select" .

An alternative might be to pg_dump just that table, edit the .sql file, dro= p the table, and then restore.

Kurt

From: Colin 't Hart <col= inthart@gmail.com>
Sent: Wednesday, October 29, 2025 9:40 AM
To: PostgreSQL General <pgsql-general@lists.postgresql.org> Subject: Re: Two sequences associated with one identity column

As expected the dump contains:

CREATE TABLE <schema>.<tablename> (
<other columns>,
    id bigint NOT NULL
);

<snip>

--
-- Name: <sequence1>; Type: SEQUENCE; Schema: <schema>; Owner: = <owner>
--

ALTER TABLE <schema>.<tablename> ALTER COLUMN id ADD GENERATED = ALWAYS
AS IDENTITY (
    SEQUENCE NAME <schema>.<sequence1>
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

--
-- Name: <sequence2>; Type: SEQUENCE; Schema: <schema>; Owner: = <owner>
--

ALTER TABLE <schema>.<tablename> ALTER COLUMN id ADD GENERATED = ALWAYS
AS IDENTITY (
    SEQUENCE NAME <schema>.<sequence2>
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

Two things are interesting:
1. The id column is last, so quite possibly added later (instead of
the original PK which was dropped?)
2. The two sequences are just dumped -- which causes an error when importin= g.

A third thing that is interesting is that I can drop the table just
fine -- and both sequences get dropped along with it.

The table seems to be relatively small -- and has no foreign keys --
so I think the solution will be to recreate the table (create table as
select), drop the original table and finally rename the new table the
same as the old one.

Also checking to see if the problem extends to the other environments.

/Colin

On Wed, 29 Oct 2025 at 13:07, hubert depesz lubaczewski
<depesz@depesz.com> wrote:
>
> On Wed, Oct 29, 2025 at 01:04:48PM +0100, Colin 't Hart wrote:
> > Thanks. But as I wrote above, trying to alter either of the two > > sequences and specifying "owned by none" results in the= error.
>
> Sorry, missed that.
>
> Can you please provide pg_dump output from this db, just schema, just<= br> > this one table, and both sequences?
>
> Or, how did you arrive at this situation?
>
> Did you try to alter table =85 alter column =85 drop identity;
>
> Best regards,
>
> depesz
>

--_000_BN8PR07MB5905FE3F05381DDBFFDEDA9FD3FAABN8PR07MB5905namp_-- From adrian.klaver@aklaver.com Fri May 15 23:23:27 2026 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 1vE92R-006Bv7-9P for pgsql-general@arkaria.postgresql.org; Wed, 29 Oct 2025 16:28:42 +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 1vE92P-002HQU-5T for pgsql-general@arkaria.postgresql.org; Wed, 29 Oct 2025 16:28:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vE92O-002HQM-9y for pgsql-general@lists.postgresql.org; Wed, 29 Oct 2025 16:28:39 +0000 Received: from fhigh-a8-smtp.messagingengine.com ([103.168.172.159]) by makus.postgresql.org with smtp (Exim 4.96) (envelope-from ) id 1vE92L-004Pgf-1Q for pgsql-general@lists.postgresql.org; Wed, 29 Oct 2025 16:28:38 +0000 Received: from phl-compute-10.internal (phl-compute-10.internal [10.202.2.50]) by mailfhigh.phl.internal (Postfix) with ESMTP id 5DCDE14000DA; Wed, 29 Oct 2025 12:28:36 -0400 (EDT) Received: from phl-mailfrontend-01 ([10.202.2.162]) by phl-compute-10.internal (MEProxy); Wed, 29 Oct 2025 12:28:36 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm2; t=1761755316; x=1761841716; bh=hxjqGDv9d4EfewFpJ3Bcy00D1SBm7aPI8JDPyLiIhe8=; b= K6lNcNBgW2TBGCyEaiSw5ioP4wFNaZ9EHRsmSIpWjPvBKpEHUpsvX8iGjdQfw+mY 8tbj8CHBPISjpacoDZO2cRtnH9lTBlMmHzPVXaiBvL9hT7MuPrcU/dGXSLjdi9K/ vhY3THkSUhLJ+yXRC14y8OypON8Kv5aCqbcxyoarl6c2nUiLvd+hYXU/MGU7F2nE 4gcQTXK4INiiLi/2hDn7qihEo2WlenjdcsOvdvsO2re5z+BOIe6te1dWVgtqrbcn ag+aWDcQX+6AhzmtF2MGxqUmqeZCnU0ysrSbzmEM2/sJxvmI6fOEK8ce7re0ZAXS kJPjG0iaHUc3mW5hahEl5w== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:subject:subject:to:to:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm3; t=1761755316; x=1761841716; bh=h xjqGDv9d4EfewFpJ3Bcy00D1SBm7aPI8JDPyLiIhe8=; b=TyPYUjCx06HuvCMbO ZriK/7pDpHoHyjl7RXb7hed6i4U7VM8woCEwPKjoPJKTKFWrYl7rILOawPtLbn0O dqb4A5eJbKLLyGZPaCh6oMlGXXPRnMzOGUMxwBvLrPlKi1sZNTtdoEvCmuyS/dcI zyxHF5bQsdz2Qlw4mDMRRxQN+jFIRaX93GJKfdoZgyuvt3lQbFRv/6GH6i89rIpc vNSAkBaUaQ4pU1D+yD/AgG33h4/JXUdxw7f5QrV4p0CXsb4qKLwrmM1LukGZNSus NQsEnCfrKv284TsfkVsMiHfIdXABPu8SzUABgE6KevjWv6ueGrT0nijuzcotoYx6 XDpSQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeeffedrtdeggdduieegvdduucetufdoteggodetrf dotffvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfurfetoffkrfgpnffqhgenuceu rghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmnecujf gurhepkfffgggfuffvfhfhjggtgfesthejredttddvjeenucfhrhhomheptegurhhirghn ucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhmqe enucggtffrrghtthgvrhhnpeeivdfhieehheegueeileejieettdejhedugeefleekvdel keehtdfgiefffeekudenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrih hlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhmpdhnsggp rhgtphhtthhopedvpdhmohguvgepshhmthhpohhuthdprhgtphhtthhopegtohhlihhnth hhrghrthesghhmrghilhdrtghomhdprhgtphhtthhopehpghhsqhhlqdhgvghnvghrrghl sehlihhsthhsrdhpohhsthhgrhgvshhqlhdrohhrgh X-ME-Proxy:

Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 29 Oct 2025 12:28:34 -0400 (EDT) Message-ID: Date: Wed, 29 Oct 2025 09:28:33 -0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Two sequences associated with one identity column To: Colin 't Hart , PostgreSQL General References:

Content-Language: en-US From: Adrian Klaver In-Reply-To: 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 10/29/25 06:40, Colin 't Hart wrote: > As expected the dump contains: > > CREATE TABLE . ( > , > id bigint NOT NULL > ); > > > > -- > -- Name: ; Type: SEQUENCE; Schema: ; Owner: > -- > > ALTER TABLE . ALTER COLUMN id ADD GENERATED ALWAYS > AS IDENTITY ( > SEQUENCE NAME . > START WITH 1 > INCREMENT BY 1 > NO MINVALUE > NO MAXVALUE > CACHE 1 > ); > > > -- > -- Name: ; Type: SEQUENCE; Schema: ; Owner: > -- > > ALTER TABLE . ALTER COLUMN id ADD GENERATED ALWAYS > AS IDENTITY ( > SEQUENCE NAME . > START WITH 1 > INCREMENT BY 1 > NO MINVALUE > NO MAXVALUE > CACHE 1 > ); Just a wild guess. Assuming is the same, what is the output of: SELECT relname, relnamespace, relpersistence FROM pg_class WHERE relname IN ('', ' ') AND relnamespace = ''::regnamespace; > > /Colin > -- Adrian Klaver adrian.klaver@aklaver.com