public inbox for [email protected]  

help / color / mirror / Atom feed

From: Colin 't Hart <[email protected]>
To: PostgreSQL General <[email protected]>
Subject: Re: Two sequences associated with one identity column
Date: Wed, 29 Oct 2025 14:40:03 +0100
Message-ID: <CAMon-aQ0Zs-Otkp1=zk3WYt7Am-_oQHKEmD_rZ1CNGBAgM_jzw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAMon-aTbZC5k2pLpijk5x+h__LV08DzpoJtHBgZGkOKJNJ91pw@mail.gmail.com>
	<[email protected]>
	<CAMon-aTMgbwC9j-wY8iX5kZ4=KXzCXc-Wpwx3viid8hQ_bJkoQ@mail.gmail.com>
	<[email protected]>

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 importing.

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
<[email protected]> 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
> this one table, and both sequences?
>
> Or, how did you arrive at this situation?
>
> Did you try to alter table … alter column … drop identity;
>
> Best regards,
>
> depesz
>

view thread (6+ messages)  latest in thread

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: Two sequences associated with one identity column
  In-Reply-To: <CAMon-aQ0Zs-Otkp1=zk3WYt7Am-_oQHKEmD_rZ1CNGBAgM_jzw@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