[BUG] 5.4-SNAPSHOT: PostgreSQL CREATE INDEX key and trailing clauses are missing #2525

Failing SQL feature

Several PostgreSQL index-key and trailing clauses either fail parsing or are available only as unstructured strings.

SQL examples

CREATE INDEX pg_idx_sort
    ON pg_index_test (id DESC NULLS FIRST);

CREATE UNIQUE INDEX pg_idx_nulls
    ON pg_index_test (email) INCLUDE (payload) NULLS NOT DISTINCT;

CREATE INDEX pg_idx_with
    ON pg_index_test (id)
    WITH (fillfactor = 80) TABLESPACE pg_default WHERE active;

CREATE INDEX pg_idx_collate
    ON pg_index_test (email COLLATE "C" text_pattern_ops);

Actual behavior

Tested with JSqlParser 5.4-SNAPSHOT at 9a32ff568f580178d9a27b101596aa43eb45d0b7:

• NULLS FIRST fails at NULLS.
• index-level NULLS NOT DISTINCT fails at NULLS.
• the storage/tablespace/predicate example fails at predicate identifier active.
• COLLATE and the operator class parse, but are flattened into Index.ColumnParams.getParams() as [COLLATE, "C", text_pattern_ops]. Existing INCLUDE and other suffixes are similarly exposed through CreateIndex.tailParameters strings.

Expected behavior

Parse and deparse the documented clauses and expose structured properties for key collation, operator class/options, sort/null ordering, included columns, null-distinct behavior, storage parameters, tablespace, and predicate expression.

Software information and server validation

• JSqlParser: 5.4-SNAPSHOT
• Database: PostgreSQL 18.6 (postgres:18)
• All four statements executed successfully. pg_indexes.indexdef confirmed the key expression, INCLUDE columns, NULLS NOT DISTINCT, fillfactor, and predicate.
• Grammar reference: https://www.postgresql.org/docs/18/sql-createindex.html

The CONCURRENTLY, optional-name, and ON ONLY header forms are tracked in #2524.

AI assistance

OpenAI Codex was used to assemble and execute the parser/server comparison. The cases were checked against the PostgreSQL 18 documentation and an actual PostgreSQL 18.6 server.