Feedback-ID: i76984098:Fastmail
Message-ID: <93c76800-58f6-432f-825e-863e2410e3f5@aklaver.com>
Date: Wed, 29 Oct 2025 07:59:37 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Two sequences associated with one identity column
To: "kurt thepw.com" <kurt@thepw.com>, Colin 't Hart <colinthart@gmail.com>,
 PostgreSQL General <pgsql-general@lists.postgresql.org>
References: <CAMon-aTbZC5k2pLpijk5x+h__LV08DzpoJtHBgZGkOKJNJ91pw@mail.gmail.com>
 <aQICcMvkp96kenL8@depesz.com>
 <CAMon-aTMgbwC9j-wY8iX5kZ4=KXzCXc-Wpwx3viid8hQ_bJkoQ@mail.gmail.com>
 <aQIDbBO0nJTiMd5C@depesz.com>
 <CAMon-aQ0Zs-Otkp1=zk3WYt7Am-_oQHKEmD_rZ1CNGBAgM_jzw@mail.gmail.com>
 <BN8PR07MB5905FE3F05381DDBFFDEDA9FD3FAA@BN8PR07MB5905.namprd07.prod.outlook.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <BN8PR07MB5905FE3F05381DDBFFDEDA9FD3FAA@BN8PR07MB5905.namprd07.prod.outlook.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/93c76800-58f6-432f-825e-863e2410e3f5%40aklaver.com>
Precedence: bulk

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
adrian.klaver@aklaver.com