Description

ItsDeadlyProgrammer

opened on Oct 23, 2026

Issue body actions

These statements are valid in PostgreSQL. pg_dump writes the first one for every deferrable foreign key. CREATE TABLE accepts the same constraint attributes, ALTER TABLE ... ADD CONSTRAINT does not.

JSqlParser: current master (90ac3a7), JDK 25.

CCJSqlParserUtil.parse("ALTER TABLE ONLY public.t ADD CONSTRAINT t_fk FOREIGN KEY (a_id) REFERENCES public.a(id) DEFERRABLE INITIALLY DEFERRED");

Statement	Result
ALTER TABLE t ADD CONSTRAINT fk FOREIGN KEY (a) REFERENCES u (id) DEFERRABLE	parses
... FOREIGN KEY (a) REFERENCES u (id) DEFERRABLE INITIALLY DEFERRED	Encountered: <S_IDENTIFIER> / "INITIALLY"
... FOREIGN KEY (a) REFERENCES u (id) INITIALLY IMMEDIATE	Encountered: <S_IDENTIFIER> / "INITIALLY"
ALTER TABLE t ADD CONSTRAINT t_uq UNIQUE (a) DEFERRABLE INITIALLY DEFERRED	Encountered: "INITIALLY"
ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (a) DEFERRABLE INITIALLY IMMEDIATE	Encountered: "INITIALLY"
ALTER TABLE t ADD CONSTRAINT t_ck CHECK (a > 0) NOT VALID	Encountered: <K_NOT> / "NOT"
ALTER TABLE t ADD FOREIGN KEY (a) REFERENCES u (id) DEFERRABLE INITIALLY DEFERRED	Encountered: <K_DEFERRABLE> / "DEFERRABLE" (without CONSTRAINT name, even plain DEFERRABLE fails)
CREATE TABLE t (a int, CONSTRAINT fk FOREIGN KEY (a) REFERENCES u (id) DEFERRABLE INITIALLY DEFERRED)	parses

The CREATE TABLE path goes through PostgreSqlConstraintAttributeList (the INITIALLY and NOT VALID handling in JSqlParserCC.jjt). The ALTER TABLE ... ADD production seems not to call it for all constraint kinds.

Found while checking a list of about 200 statements against master. Most of them parsed and round-tripped.

Activity

1. hutiefang76 commented on Oct 24, 2026

   

   hutiefang76

   on Oct 24, 2026

   Contributor

   More actions

   These statements work on current master (220d0ed4) when the PostgreSQL dialect is enabled:

   import net.sf.jsqlparser.parser.AbstractJSqlParser.Dialect;
   import net.sf.jsqlparser.parser.CCJSqlParserUtil;
   
   CCJSqlParserUtil.parse(sql, parser -> parser.withDialect(Dialect.POSTGRESQL));

   The ALTER TABLE constraint support in #2612 is gated by that dialect. I ran all eight SQL statements from your table on an unmodified build of this revision: all eight parsed and could be deparsed and parsed again in PostgreSQL mode; default mode reproduced the six failures. The ALTER TABLE ONLY t ... variant also passed with PostgreSQL enabled.

   Could you try the explicit dialect in your caller?

   _{AI-assisted investigation; results are from an actual Java 17 run.}
2. ItsDeadlyProgrammer commented on Oct 24, 2026

   

   ItsDeadlyProgrammer

   on Oct 24, 2026

   Author

   More actions

   Thanks, confirmed. On current master (220d0ed4), all eight statements parse with parser.withDialect(Dialect.POSTGRESQL), including ALTER TABLE ONLY and the form with no constraint name. In default mode, only plain DEFERRABLE parses.

   One question: CREATE TABLE accepts the same INITIALLY ... and NOT VALID attributes without the dialect. Is it intentional that ALTER TABLE ... ADD needs the PostgreSQL dialect for them? If so, I'm happy to close this. If you'd prefer default mode to accept them too, I can send a PR.

Issue actions