public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: kurt thepw.com <[email protected]>
To: Colin 't Hart <[email protected]>
To: PostgreSQL General <[email protected]>
Subject: Re: Two sequences associated with one identity column
Date: Wed, 29 Oct 2025 07:59:37 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <BN8PR07MB5905FE3F05381DDBFFDEDA9FD3FAA@BN8PR07MB5905.namprd07.prod.outlook.com>
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>

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]

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], [email protected], [email protected]
  Subject: Re: Two sequences associated with one identity column
  In-Reply-To: <[email protected]>

* 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