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 1vE8A3-005wp3-CS for pgsql-general@arkaria.postgresql.org; Wed, 29 Oct 2025 15:32:30 +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 1vE8A2-001rx4-8a for pgsql-general@arkaria.postgresql.org; Wed, 29 Oct 2025 15:32:29 +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 1vE8A1-001rww-Q3 for pgsql-general@lists.postgresql.org; Wed, 29 Oct 2025 15:32:28 +0000 Received: from mail-oi1-x22f.google.com ([2607:f8b0:4864:20::22f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vE89z-004PGD-0C for pgsql-general@lists.postgresql.org; Wed, 29 Oct 2025 15:32:27 +0000 Received: by mail-oi1-x22f.google.com with SMTP id 5614622812f47-44d9eca1d57so8010b6e.1 for ; Wed, 29 Oct 2025 08:32:26 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1761751946; x=1762356746; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=aMCmCfMHX986Q8InuQ4SobYMN46bpTQ568kMsJJuawc=; b=BLeR6NgGQViSSKLusTDdmTlChonouR8zkODSKndL/GRQxzipZLfKNPW61CRxj2vi/F hxUUKFcbYDoRWS248ckY4EgTNIbhW0j12bUDiM3hRqyqmUT+x9HouUcCJ9YSlVm9Qi+m kiCxObLh7nWCBrUdNO+25LYxh7pLQQQz8CJZfei+qg7uzKyHdJoGdU8NQRWxGmdkUmT5 khn2Xc5UzZdXR2PmOzPLljNzSoOqsrPpDyBSynxtpxWcnN7kCa+3I7Mfp8zJuBUlT3hf 6hiRHotm1aCqtzc5FiVBzrHfKctjZGToM84feDis6HEvknyd5IMDoQb03tpAzjv7EblZ gaLg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1761751946; x=1762356746; h=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=aMCmCfMHX986Q8InuQ4SobYMN46bpTQ568kMsJJuawc=; b=FwKysIJaZeisBuMwX0QgTl6ZKIHehyRSunkc77EkoBo7niJtgdiF6bz0zSm7bqJ9rq nUWg5n8+LKWmkQs6DfEwtTj+6kZUVea/heCI2a4D5WMkwdCduGXsRGXV6WFAxH+5Oscy LDYyBOti9hJg128O6YsDZEt2fb3QYSii8xdDgu77ygKrck3edJn5FWiztPjFC/8neQ1s Bq2AhTpUBnC6J/0QKQLzF8EiThOq01ayal/F7UMBc5bR/3YCCO4AbWAE4JsOVwzWlqZm TtCeTPIkrzx5t1TJBwdn7LTiFCmPji+tfDar8WK0IeGR5bQ92VdwcKHEpPlAmATt7AVx PWVg== X-Gm-Message-State: AOJu0YzvsU2WSasmTUwoaBmA3HY6XAzoBeyZzl5TE2TtR48dc2wnwUxn /QFgfdPxhIsrjbkFOzv3+j2Lvoc7gjuYUJMa88MP/QkQy9Rgp7hJyRWseapucI6zoFL/11HobK1 XM1/PwcbO2G6rTH3nheYA6urErasRjnqKpcyZ X-Gm-Gg: ASbGncv+XoHJfxx9U2I2aJVf4cs0goGW8OAtMErBI59beViL/Ccv3sKhSZXg+Ef/Seq L/td+5pqQlz/VSfuM02brdZ7+NyPLpL5s3/TyNPpeEZZuRIpfwqylT/pdDU5ICMHlVTtDIZLy90 3CCrWQwfYBnA3xrF0jezH8mLLseY5pbYrMEqsVGQtxfmQ4vRo1DORKjw/rnhn6Mb9v2oXr9tkNv mAcrfGqt59hW84X9mFSCsLQXsaYs1PTrIM+H8Va8edVFw9FTobUKRZxK6ccGg== X-Google-Smtp-Source: AGHT+IHZmm0wgTzSVnZ0kKwK6lSTtGEgmXayAAhN66CvkofIBzsCzk/Ju504KjqHEovSNafZmhLbi/rAjgv/CnbLFWY= X-Received: by 2002:a05:6808:1983:b0:44d:ab76:bb56 with SMTP id 5614622812f47-44f7a7aa1b2mr1621838b6e.28.1761751945941; Wed, 29 Oct 2025 08:32:25 -0700 (PDT) MIME-Version: 1.0 References: <93c76800-58f6-432f-825e-863e2410e3f5@aklaver.com> In-Reply-To: <93c76800-58f6-432f-825e-863e2410e3f5@aklaver.com> From: Ron Johnson Date: Wed, 29 Oct 2025 11:32:14 -0400 X-Gm-Features: AWmQ_bl263wTe0K7RP0ZE9eTP6dnX6qtIfUbcA3Un_-zYlftBpafCOwRE6UVANE Message-ID: Subject: Re: Two sequences associated with one identity column To: PostgreSQL General Content-Type: multipart/alternative; boundary="000000000000eb97ec06424dd698" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000eb97ec06424dd698 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM Adrian Klaver wrote: > On 10/29/25 07:47, kurt thepw.com wrote: > > > > < > > < CREATE TABLE . ( > > < , > > < 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 . ( > > < id integer DEFAULT nextval('.'::regclass) NOT NUL= L, > > < ..., > > < . . . . . > > < ); > > 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 . ALTER COLUMN id ADD GENERATED..." > > > > Statements. > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000eb97ec06424dd698 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I'd have expected the CREATE SEQUENCE and ALTER T= ABLE 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 p= g_dump.=C2=A0 After all, what really matters is the destination, not the jo= urney.

On Wed, Oct 29, 2025 at 10:59=E2=80=AFAM Adri= an Klaver <adrian.klaver@ak= laver.com> wrote:
On 10/29/25 07:47, kurt thepw.com wrote:
>
> <
> < CREATE TABLE <schema>.<tablename> (
> < <other columns>,
> <=C2=A0 =C2=A0id bigint NOT NULL
> < );
> <
>
> I've never seen a plaintext pg_dump=C2=A0 output where the sequenc= e
> associated with a column in a table was not mentioned in s "DEFAU= LT
> nextval(..." modifier in that column's line of the CREATE TAB= LE
> statement, ex:
>
> <
> < CREATE TABLE <schema>.<tbl> (
> <=C2=A0 =C2=A0 id integer DEFAULT nextval('<schema>.<s= eqname>'::regclass) NOT NULL,
> <=C2=A0 =C2=A0 <next column>...,
> <=C2=A0 =C2=A0.=C2=A0 .=C2=A0 .=C2=A0 .=C2=A0 .
> <=C2=A0 );

That is for case where someone manually creates DEFAULT:

create table manual_seq_test(id integer default nextval('test_seq')= ,
fld_1 varchar,=C2=A0 fld_2 boolean);

pg_dump -d test -U postgres -p 5432 -t manual_seq_test

CREATE TABLE public.manual_seq_test (
=C2=A0 =C2=A0 =C2=A0id integer DEFAULT nextval('public.test_seq'::r= egclass),
=C2=A0 =C2=A0 =C2=A0fld_1 character varying,
=C2=A0 =C2=A0 =C2=A0fld_2 boolean
);

Otherwise for system generated sequences you get:

create table seq_test(id serial, fld_1 varchar,=C2=A0 fld_2 boolean);

CREATE TABLE public.seq_test (
=C2=A0 =C2=A0 =C2=A0id integer NOT NULL,
=C2=A0 =C2=A0 =C2=A0fld_1 character varying,
=C2=A0 =C2=A0 =C2=A0fld_2 boolean
);


CREATE SEQUENCE public.seq_test_id_seq
=C2=A0 =C2=A0 =C2=A0AS integer
=C2=A0 =C2=A0 =C2=A0START WITH 1
=C2=A0 =C2=A0 =C2=A0INCREMENT BY 1
=C2=A0 =C2=A0 =C2=A0NO MINVALUE
=C2=A0 =C2=A0 =C2=A0NO MAXVALUE
=C2=A0 =C2=A0 =C2=A0CACHE 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,=C2=A0 fld_2 boolean);

CREATE TABLE public.id_test (
=C2=A0 =C2=A0 =C2=A0id integer NOT NULL,
=C2=A0 =C2=A0 =C2=A0fld_1 character varying,
=C2=A0 =C2=A0 =C2=A0fld_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 (
=C2=A0 =C2=A0 =C2=A0SEQUENCE NAME public.id_test_id_seq
=C2=A0 =C2=A0 =C2=A0START WITH 1
=C2=A0 =C2=A0 =C2=A0INCREMENT BY 1
=C2=A0 =C2=A0 =C2=A0NO MINVALUE
=C2=A0 =C2=A0 =C2=A0NO MAXVALUE
=C2=A0 =C2=A0 =C2=A0CACHE 1
);


>
> With the sequence already created earlier in the dump file. But then, =
> I've never before seen a table column with two associated sequence= s.
> Maybe that is what makes pg_dump generate the
>
> "ALTER TABLE <schema>.<tablename> ALTER COLUMN id ADD= GENERATED..."
>
> Statements.

--
Adrian Klaver
adrian.klave= r@aklaver.com




--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--000000000000eb97ec06424dd698--