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]
