public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ron Johnson <[email protected]>
To: PostgreSQL General <[email protected]>
Subject: Re: Two sequences associated with one identity column
Date: Wed, 29 Oct 2025 11:32:14 -0400
Message-ID: <CANzqJaDhmgJS1LASQ-+dWbUANfpZwKnB=c4=oW-Dv=9ehRXHEA@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]>
	<CAMon-aQ0Zs-Otkp1=zk3WYt7Am-_oQHKEmD_rZ1CNGBAgM_jzw@mail.gmail.com>
	<BN8PR07MB5905FE3F05381DDBFFDEDA9FD3FAA@BN8PR07MB5905.namprd07.prod.outlook.com>
	<[email protected]>

I'd have expected the CREATE SEQUENCE and ALTER TABLE to be separate that
can go in the post-data section, and be there even in schema-only dumps
because it was easier for whoever added sections to pg_dump.  After all,
what really matters is the destination, not the journey.

On Wed, Oct 29, 2025 at 10:59 AM Adrian Klaver <[email protected]>
wrote:

> On 10/29/25 07:47, kurt thepw.com wrote:
> >
> > <
> > < CREATE TABLE <schema>.<tablename> (
> > < <other columns>,
> > <   id bigint NOT NULL
> > < );
> > <
> >
> > I've never seen a plaintext pg_dump  output where the sequence
> > associated with a column in a table was not mentioned in s "DEFAULT
> > nextval(..." modifier in that column's line of the CREATE TABLE
> > statement, ex:
> >
> > <
> > < CREATE TABLE <schema>.<tbl> (
> > <    id integer DEFAULT nextval('<schema>.<seqname>'::regclass) NOT NULL,
> > <    <next column>...,
> > <   .  .  .  .  .
> > <  );
>
> That is for case where someone manually creates DEFAULT:
>
> create table manual_seq_test(id integer default nextval('test_seq'),
> fld_1 varchar,  fld_2 boolean);
>
> pg_dump -d test -U postgres -p 5432 -t manual_seq_test
>
> CREATE TABLE public.manual_seq_test (
>      id integer DEFAULT nextval('public.test_seq'::regclass),
>      fld_1 character varying,
>      fld_2 boolean
> );
>
> Otherwise for system generated sequences you get:
>
> create table seq_test(id serial, fld_1 varchar,  fld_2 boolean);
>
> CREATE TABLE public.seq_test (
>      id integer NOT NULL,
>      fld_1 character varying,
>      fld_2 boolean
> );
>
>
> CREATE SEQUENCE public.seq_test_id_seq
>      AS integer
>      START WITH 1
>      INCREMENT BY 1
>      NO MINVALUE
>      NO MAXVALUE
>      CACHE 1;
>
>
> ALTER SEQUENCE public.seq_test_id_seq OWNER TO postgres;
>
> --
> -- Name: seq_test_id_seq; Type: SEQUENCE OWNED BY; Schema: public;
> Owner: postgres
> --
>
> ALTER SEQUENCE public.seq_test_id_seq OWNED BY public.seq_test.id;
>
>
> --
> -- Name: seq_test id; Type: DEFAULT; Schema: public; Owner: postgres
> --
>
> ALTER TABLE ONLY public.seq_test ALTER COLUMN id SET DEFAULT
> nextval('public.seq_test_id_seq'::regclass);
>
>
> OR
>
> create table id_test(id integer generated always as identity, fld_1
> varchar,  fld_2 boolean);
>
> CREATE TABLE public.id_test (
>      id integer NOT NULL,
>      fld_1 character varying,
>      fld_2 boolean
> );
>
>
> ALTER TABLE public.id_test OWNER TO postgres;
>
> --
> -- Name: id_test_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
> --
>
> ALTER TABLE public.id_test ALTER COLUMN id ADD GENERATED ALWAYS AS
> IDENTITY (
>      SEQUENCE NAME public.id_test_id_seq
>      START WITH 1
>      INCREMENT BY 1
>      NO MINVALUE
>      NO MAXVALUE
>      CACHE 1
> );
>
>
> >
> > With the sequence already created earlier in the dump file. But then,
> > I've never before seen a table column with two associated sequences.
> > Maybe that is what makes pg_dump generate the
> >
> > "ALTER TABLE <schema>.<tablename> ALTER COLUMN id ADD GENERATED..."
> >
> > Statements.
>
> --
> Adrian Klaver
> [email protected]
>
>
>

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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: <CANzqJaDhmgJS1LASQ-+dWbUANfpZwKnB=c4=oW-Dv=9ehRXHEA@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