A table is correctly defined by the developer using: mytabname_id SERIAL NOT NULL, ...
PostgreSQL internally expands serial into: integer NOT NULL DEFAULT nextval('mytabname_id_seq'::regclass), ...
This is expected.
However, when using Tools → Schema Diff → Generate Script, pgAdmin attempts to reconstruct the DDL but produces an invalid SQL fragment, such as: mytabname_id integer NOT NULL DEFAULT nextval('mytabname_id_seq'::regclass), ...
the script fails during execution, blocking schema synchronization
pgAdmin should either preserve serial or generate a valid equivalent DDL
Steps to Reproduce
Create a table with a SERIAL column (e.g. "doc_id SERIAL NOT NULL").
Use Schema Diff to compare source and target databases.
Click Generate Script.
Inspect the DDL generated for the SERIAL column
Expected Behavior
pgAdmin should generate: "doc_id SERIAL NOT NULL,"
Error message
ERROR: relation "zmvcoc_zmvcoc_id_seq" does not exist
LINE 3: zmvcoc_id integer NOT NULL DEFAULT nextval('zmvcoc_zmvco...
^
ERRORE: relation "zmvcoc_zmvcoc_id_seq" does not exist
SQL state: 42P01
Character: 91
Screenshots
Generated Script:
CREATE TABLE IF NOT EXISTS public.zmvcoc
(
zmvcoc_id integer NOT NULL DEFAULT nextval('zmvcoc_zmvcoc_id_seq'::regclass),
zmvcoc_usr character varying(20) COLLATE pg_catalog."default",
zmvcoc_zmvco_id integer NOT NULL,
zmvcoc_cd character varying(20) COLLATE pg_catalog."default",
zmvcoc_des character varying(80) COLLATE pg_catalog."default",
zmvcoc_pc integer DEFAULT 100,
CONSTRAINT zmvcoc_pk PRIMARY KEY (zmvcoc_id),
CONSTRAINT zmvcoc_zmvco_id_fk FOREIGN KEY (zmvcoc_zmvco_id)
REFERENCES public.zmvco (zmvco_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS public.zmvcoc
OWNER to postgres;
COMMENT ON TABLE public.zmvcoc
IS 'Tabella suddivisione costi';
Desktop (please complete the following information):
- OS: Windows 11 Ver.22H2
- pgAdmin version: 9.14
- Mode: Desktop
- Browser N.A.
- Package type: exe
Additional context
None
A table is correctly defined by the developer using: mytabname_id SERIAL NOT NULL, ...
PostgreSQL internally expands serial into: integer NOT NULL DEFAULT nextval('mytabname_id_seq'::regclass), ...
This is expected.
However, when using Tools → Schema Diff → Generate Script, pgAdmin attempts to reconstruct the DDL but produces an invalid SQL fragment, such as: mytabname_id integer NOT NULL DEFAULT nextval('mytabname_id_seq'::regclass), ...
the script fails during execution, blocking schema synchronization
pgAdmin should either preserve serial or generate a valid equivalent DDL
Steps to Reproduce
Create a table with a SERIAL column (e.g. "doc_id SERIAL NOT NULL").
Use Schema Diff to compare source and target databases.
Click Generate Script.
Inspect the DDL generated for the SERIAL column
Expected Behavior
pgAdmin should generate: "doc_id SERIAL NOT NULL,"
Error message
ERROR: relation "zmvcoc_zmvcoc_id_seq" does not exist
LINE 3: zmvcoc_id integer NOT NULL DEFAULT nextval('zmvcoc_zmvco...
^
ERRORE: relation "zmvcoc_zmvcoc_id_seq" does not exist
SQL state: 42P01
Character: 91
Screenshots
Generated Script:
CREATE TABLE IF NOT EXISTS public.zmvcoc
(
zmvcoc_id integer NOT NULL DEFAULT nextval('zmvcoc_zmvcoc_id_seq'::regclass),
zmvcoc_usr character varying(20) COLLATE pg_catalog."default",
zmvcoc_zmvco_id integer NOT NULL,
zmvcoc_cd character varying(20) COLLATE pg_catalog."default",
zmvcoc_des character varying(80) COLLATE pg_catalog."default",
zmvcoc_pc integer DEFAULT 100,
CONSTRAINT zmvcoc_pk PRIMARY KEY (zmvcoc_id),
CONSTRAINT zmvcoc_zmvco_id_fk FOREIGN KEY (zmvcoc_zmvco_id)
REFERENCES public.zmvco (zmvco_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE CASCADE
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS public.zmvcoc
OWNER to postgres;
COMMENT ON TABLE public.zmvcoc
IS 'Tabella suddivisione costi';
Desktop (please complete the following information):
Additional context
None