Re: Two sequences associated with one identity column - Mailing list pgsql-general

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