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 <[email protected]> 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 > [email protected] > > > -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster!
