Re: Two sequences associated with one identity column

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 <adrian(dot)klaver(at)aklaver(dot)com>
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
> adrian(dot)klaver(at)aklaver(dot)com
>
>
>

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