This is an automated email from the ASF dual-hosted git repository.
strongduanmu pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new a726e41a574 Support PostgreSQL constraints and generated columns
(#40205)
a726e41a574 is described below
commit a726e41a5744cdc7a0cc1bc0bea90651722b39e1
Author: ZhangCheng <[email protected]>
AuthorDate: Tue Oct 20 14:08:07 2026 +0800
Support PostgreSQL constraints and generated columns (#40205)
* Support PostgreSQL constraints and generated columns
* Keep PostgreSQL nullsDistinct rule in upstream position
* Fix PostgreSQL constraint parsing and regression expectations
* Remove invalid PostgreSQL constraint trigger test cases
---
.../main/antlr4/imports/postgresql/DDLStatement.g4 | 23 +-
.../type/PostgreSQLDDLStatementVisitor.java | 39 ++-
.../definition/ConstraintDefinitionAssert.java | 4 +
.../standard/type/AlterTableStatementAssert.java | 4 +
.../definition/ExpectedConstraintDefinition.java | 3 +
.../table/AlterTableStatementTestCase.java | 4 +
.../src/main/resources/case/ddl/alter-table.xml | 136 ++++++++++
.../src/main/resources/case/ddl/create-index.xml | 16 ++
.../src/main/resources/case/ddl/create-table.xml | 292 +++++++++++++++++++++
.../src/main/resources/case/ddl/create-trigger.xml | 41 +++
.../resources/case/ddl/postgresql-regression.xml | 1 +
.../resources/sql/supported/ddl/alter-table.xml | 19 ++
.../resources/sql/supported/ddl/create-index.xml | 2 +
.../resources/sql/supported/ddl/create-table.xml | 70 +++++
.../resources/sql/supported/ddl/create-trigger.xml | 7 +
15 files changed, 643 insertions(+), 18 deletions(-)
diff --git
a/parser/sql/engine/dialect/postgresql/src/main/antlr4/imports/postgresql/DDLStatement.g4
b/parser/sql/engine/dialect/postgresql/src/main/antlr4/imports/postgresql/DDLStatement.g4
index 8c8df6caf4e..146e660293a 100644
---
a/parser/sql/engine/dialect/postgresql/src/main/antlr4/imports/postgresql/DDLStatement.g4
+++
b/parser/sql/engine/dialect/postgresql/src/main/antlr4/imports/postgresql/DDLStatement.g4
@@ -193,11 +193,19 @@ createDefinitionClause
;
createDefinition
- : columnDefinition | tableConstraint | LIKE tableName likeOption*
+ : createTableConstraint | columnDefinition | LIKE tableName likeOption*
+ ;
+
+createTableConstraint
+ : constraintClause? tableConstraintOption constraintAttributeSpec
;
columnDefinition
- : columnName dataType (STORAGE colId)? columnCompression? collateClause?
columnConstraint*
+ : columnName dataType columnStorage? columnCompression? collateClause?
columnConstraint*
+ ;
+
+columnStorage
+ : STORAGE (colId | DEFAULT)
;
columnConstraint
@@ -209,7 +217,8 @@ constraintClause
;
columnConstraintOption
- : NOT? NULL
+ : NULL
+ | NOT NULL (NO INHERIT)?
| checkOption
| DEFAULT defaultExpr
| GENERATED ALWAYS AS LP_ aExpr RP_ (STORED | VIRTUAL)?
@@ -263,7 +272,7 @@ likeOption
;
tableConstraint
- : constraintClause? tableConstraintOption constraintOptionalParam
+ : constraintClause? tableConstraintOption constraintOptionalParam (NO
INHERIT constraintOptionalParam)*
;
tableConstraintOption
@@ -390,10 +399,10 @@ alterTableActions
;
alterTableAction
- : addColumnSpecification
+ : addConstraintSpecification
+ | addColumnSpecification
| dropColumnSpecification
| modifyColumnSpecification
- | addConstraintSpecification
| modifyConstraintSpecification
| validateConstraintSpecification
| dropConstraintSpecification
@@ -466,7 +475,7 @@ attributeOption
;
addConstraintSpecification
- : ADD (tableConstraint (NOT VALID)? | tableConstraintUsingIndex)
+ : ADD (tableConstraint (NOT VALID constraintAttributeSpec)? |
tableConstraintUsingIndex)
;
tableConstraintUsingIndex
diff --git
a/parser/sql/engine/dialect/postgresql/src/main/java/org/apache/shardingsphere/sql/parser/engine/postgresql/visitor/statement/type/PostgreSQLDDLStatementVisitor.java
b/parser/sql/engine/dialect/postgresql/src/main/java/org/apache/shardingsphere/sql/parser/engine/postgresql/visitor/statement/type/PostgreSQLDDLStatementVisitor.java
index d650139bb35..5d1ab346dec 100644
---
a/parser/sql/engine/dialect/postgresql/src/main/java/org/apache/shardingsphere/sql/parser/engine/postgresql/visitor/statement/type/PostgreSQLDDLStatementVisitor.java
+++
b/parser/sql/engine/dialect/postgresql/src/main/java/org/apache/shardingsphere/sql/parser/engine/postgresql/visitor/statement/type/PostgreSQLDDLStatementVisitor.java
@@ -80,6 +80,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.Co
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.ColumnNameContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.ColumnrefContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.CommentContext;
+import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.ConstraintClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.CountContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.CreateAccessMethodContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.CreateAggregateContext;
@@ -110,6 +111,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.Cr
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.CreateServerContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.CreateStatisticsContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.CreateSubscriptionContext;
+import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.CreateTableConstraintContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.CreateTableContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.CreateTablespaceContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.CreateTextSearchContext;
@@ -186,6 +188,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.No
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.OpenContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.PrepareContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.PriorContext;
+import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.QualifiedNameContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.RefreshMatViewStmtContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.ReindexContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.RelativeCountContext;
@@ -193,6 +196,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.Re
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.RenameTableSpecificationContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.SecurityLabelStmtContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.TableConstraintContext;
+import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.TableConstraintOptionContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.TableConstraintUsingIndexContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.TableNameClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.PostgreSQLStatementParser.TableNamesClauseContext;
@@ -432,8 +436,8 @@ public final class PostgreSQLDDLStatementVisitor extends
PostgreSQLStatementVisi
if (null != each.columnDefinition()) {
result.getValue().add((ColumnDefinitionSegment)
visit(each.columnDefinition()));
}
- if (null != each.tableConstraint()) {
- result.getValue().add((ConstraintDefinitionSegment)
visit(each.tableConstraint()));
+ if (null != each.createTableConstraint()) {
+ result.getValue().add((ConstraintDefinitionSegment)
visit(each.createTableConstraint()));
}
}
return result;
@@ -657,18 +661,29 @@ public final class PostgreSQLDDLStatementVisitor extends
PostgreSQLStatementVisi
return result;
}
- @SuppressWarnings("unchecked")
+ @Override
+ public ASTNode visitCreateTableConstraint(final
CreateTableConstraintContext ctx) {
+ return
createConstraintDefinitionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), ctx.constraintClause(),
ctx.tableConstraintOption());
+ }
+
@Override
public ASTNode visitTableConstraint(final TableConstraintContext ctx) {
- ConstraintDefinitionSegment result = new
ConstraintDefinitionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex());
- if (null != ctx.constraintClause()) {
- result.setConstraintName((ConstraintSegment)
visit(ctx.constraintClause().constraintName()));
+ return
createConstraintDefinitionSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), ctx.constraintClause(),
ctx.tableConstraintOption());
+ }
+
+ private ConstraintDefinitionSegment
createConstraintDefinitionSegment(final int startIndex, final int stopIndex,
+
final ConstraintClauseContext constraintClause, final
TableConstraintOptionContext option) {
+ ConstraintDefinitionSegment result = new
ConstraintDefinitionSegment(startIndex, stopIndex);
+ if (null != constraintClause) {
+ result.setConstraintName((ConstraintSegment)
visit(constraintClause.constraintName()));
}
- if (null != ctx.tableConstraintOption().primaryKey()) {
-
result.getPrimaryKeyColumns().addAll(((CollectionValue<ColumnSegment>)
visit(ctx.tableConstraintOption().keyColumnNames())).getValue());
+ if (null != option.primaryKey()) {
+ for (ColumnNameContext each :
option.keyColumnNames().columnName()) {
+ result.getPrimaryKeyColumns().add((ColumnSegment) visit(each));
+ }
}
- if (null != ctx.tableConstraintOption().FOREIGN()) {
- result.setReferencedTable((SimpleTableSegment)
visit(ctx.tableConstraintOption().tableName()));
+ if (null != option.FOREIGN()) {
+ result.setReferencedTable((SimpleTableSegment)
visit(option.tableName()));
}
return result;
}
@@ -967,7 +982,9 @@ public final class PostgreSQLDDLStatementVisitor extends
PostgreSQLStatementVisi
public ASTNode visitCreateTrigger(final CreateTriggerContext ctx) {
CreateTriggerStatement result = new
CreateTriggerStatement(getDatabaseType());
result.setTriggerName(new
FunctionNameSegment(ctx.name().start.getStartIndex(),
ctx.name().stop.getStopIndex(), createIdentifierValue(ctx.name().getText())));
- result.setTable((SimpleTableSegment) visit(ctx.qualifiedName(0)));
+ for (QualifiedNameContext each : ctx.qualifiedName()) {
+ result.getTables().add((SimpleTableSegment) visit(each));
+ }
return result;
}
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/definition/ConstraintDefinitionAssert.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/definition/ConstraintDefinitionAssert.java
index 00f2731b61e..7eaab5f7cb3 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/definition/ConstraintDefinitionAssert.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/segment/definition/ConstraintDefinitionAssert.java
@@ -45,6 +45,10 @@ public final class ConstraintDefinitionAssert {
* @param expected expected constraint definition
*/
public static void assertIs(final SQLCaseAssertContext assertContext,
final ConstraintDefinitionSegment actual, final ExpectedConstraintDefinition
expected) {
+ if (null != expected.getPrimaryKeyColumnCount()) {
+ assertThat(assertContext.getText("Expected primary key columns
size assertion error: "), expected.getPrimaryKeyColumns().size(),
is(expected.getPrimaryKeyColumnCount()));
+ assertThat(assertContext.getText("Primary key columns size
assertion error: "), actual.getPrimaryKeyColumns().size(),
is(expected.getPrimaryKeyColumnCount()));
+ }
int count = 0;
for (ColumnSegment each : actual.getPrimaryKeyColumns()) {
ColumnAssert.assertIs(assertContext, each,
expected.getPrimaryKeyColumns().get(count));
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/ddl/standard/type/AlterTableStatementAssert.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/ddl/standard/type/AlterTableStatementAssert.java
index dc916260a9a..bbd59b55f5d 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/ddl/standard/type/AlterTableStatementAssert.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/asserts/statement/ddl/standard/type/AlterTableStatementAssert.java
@@ -251,6 +251,10 @@ public final class AlterTableStatementAssert {
}
private static void assertAddConstraintDefinitions(final
SQLCaseAssertContext assertContext, final AlterTableStatement actual, final
AlterTableStatementTestCase expected) {
+ if (null != expected.getAddConstraintCount()) {
+ assertThat(assertContext.getText("Expected add constraint
definitions size assertion error: "), expected.getAddConstraints().size(),
is(expected.getAddConstraintCount()));
+ assertThat(assertContext.getText("Add constraint definitions size
assertion error: "), actual.getAddConstraintDefinitions().size(),
is(expected.getAddConstraintCount()));
+ }
int count = 0;
for (AddConstraintDefinitionSegment each :
actual.getAddConstraintDefinitions()) {
ConstraintDefinitionAssert.assertIs(assertContext,
each.getConstraintDefinition(), expected.getAddConstraints().get(count));
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/definition/ExpectedConstraintDefinition.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/definition/ExpectedConstraintDefinition.java
index 8cc2a2ae8c8..c5ddc312dc5 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/definition/ExpectedConstraintDefinition.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/segment/impl/definition/ExpectedConstraintDefinition.java
@@ -35,6 +35,9 @@ import java.util.List;
@Setter
public final class ExpectedConstraintDefinition extends
AbstractExpectedSQLSegment {
+ @XmlAttribute(name = "primary-key-column-count")
+ private Integer primaryKeyColumnCount;
+
@XmlElement(name = "referenced-table")
private ExpectedSimpleTable referencedTable;
diff --git
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/ddl/standard/table/AlterTableStatementTestCase.java
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/ddl/standard/table/AlterTableStatementTestCase.java
index c6e0f1b2330..4a81b6fd9b0 100644
---
a/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/ddl/standard/table/AlterTableStatementTestCase.java
+++
b/test/it/parser/src/main/java/org/apache/shardingsphere/test/it/sql/parser/internal/cases/parser/jaxb/statement/ddl/standard/table/AlterTableStatementTestCase.java
@@ -49,6 +49,7 @@ import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.s
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.partition.ExpectedAddPartitions;
import
org.apache.shardingsphere.test.it.sql.parser.internal.cases.parser.jaxb.segment.impl.table.ExpectedSimpleTable;
+import javax.xml.bind.annotation.XmlAttribute;
import javax.xml.bind.annotation.XmlElement;
import java.util.LinkedList;
import java.util.List;
@@ -75,6 +76,9 @@ public final class AlterTableStatementTestCase extends
SQLParserTestCase {
@XmlElement(name = "add-column")
private final List<ExpectedAddColumnDefinition> addColumns = new
LinkedList<>();
+ @XmlAttribute(name = "add-constraint-count")
+ private Integer addConstraintCount;
+
@XmlElement(name = "add-constraint")
private final List<ExpectedConstraintDefinition> addConstraints = new
LinkedList<>();
diff --git a/test/it/parser/src/main/resources/case/ddl/alter-table.xml
b/test/it/parser/src/main/resources/case/ddl/alter-table.xml
index 750e57f86bb..350a2d9589c 100644
--- a/test/it/parser/src/main/resources/case/ddl/alter-table.xml
+++ b/test/it/parser/src/main/resources/case/ddl/alter-table.xml
@@ -3920,4 +3920,140 @@
</column-definition>
</modify-column>
</alter-table>
+
+ <alter-table sql-case-id="alter_table_add_not_enforced_pg">
+ <table name="foo_table" start-index="12" stop-index="20" />
+ <add-constraint constraint-name="positive" start-index="26"
stop-index="81" />
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_constraint_enforced_pg">
+ <table name="foo_table" start-index="12" stop-index="20" />
+ <modify-constraint constraint-name="positive" start-index="22"
stop-index="55" />
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_constraint_attribute_order_pg">
+ <table name="foo_table" start-index="12" stop-index="20" />
+ <modify-constraint constraint-name="positive" start-index="22"
stop-index="76" />
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_temporal_pk_pg">
+ <table name="foo_table" start-index="12" stop-index="20" />
+ <add-constraint primary-key-column-count="2" constraint-name="foo_pk"
start-index="26" stop-index="86">
+ <primary-key-column name="id" start-index="57" stop-index="58" />
+ <primary-key-column name="valid_at" start-index="61"
stop-index="68" />
+ </add-constraint>
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_temporal_unique_pg">
+ <table name="foo_table" start-index="12" stop-index="20" />
+ <add-constraint primary-key-column-count="0"
constraint-name="foo_unique" start-index="26" stop-index="85" />
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_temporal_foreign_key_pg">
+ <table name="foo_table" start-index="12" stop-index="20" />
+ <add-constraint primary-key-column-count="0" constraint-name="foo_fk"
start-index="26" stop-index="122">
+ <referenced-table name="parent_table" start-index="89"
stop-index="100" />
+ </add-constraint>
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_set_expression_pg">
+ <table name="t" start-index="12" stop-index="12" />
+ <modify-column>
+ <column-definition start-index="14" stop-index="55"
not-null="false">
+ <column name="b" start-index="27" stop-index="27" />
+ </column-definition>
+ </modify-column>
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_set_expression_without_column_pg">
+ <table name="t" start-index="12" stop-index="12" />
+ <modify-column>
+ <column-definition start-index="14" stop-index="46"
not-null="false">
+ <column name="b" start-index="20" stop-index="20" />
+ </column-definition>
+ </modify-column>
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_set_expression_qualified_pg">
+ <table name="t" start-index="17" stop-index="19">
+ <owner name="s" start-index="17" stop-index="17" />
+ </table>
+ <modify-column>
+ <column-definition start-index="21" stop-index="66"
not-null="false">
+ <column name="b" start-index="34" stop-index="36" />
+ </column-definition>
+ </modify-column>
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_set_expression_function_pg">
+ <table name="t" start-index="12" stop-index="12" />
+ <modify-column>
+ <column-definition start-index="14" stop-index="75"
not-null="false">
+ <column name="b" start-index="27" stop-index="27" />
+ </column-definition>
+ </modify-column>
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_set_expression_multiple_pg">
+ <table name="t" start-index="12" stop-index="12" />
+ <modify-column>
+ <column-definition start-index="14" stop-index="53"
not-null="false">
+ <column name="b" start-index="27" stop-index="27" />
+ </column-definition>
+ </modify-column>
+ <modify-column>
+ <column-definition start-index="56" stop-index="95"
not-null="false">
+ <column name="c" start-index="69" stop-index="69" />
+ </column-definition>
+ </modify-column>
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_set_expression_default_control_pg">
+ <table name="t" start-index="12" stop-index="12" />
+ <modify-column>
+ <column-definition start-index="14" stop-index="47"
not-null="false">
+ <column name="b" start-index="27" stop-index="27" />
+ </column-definition>
+ </modify-column>
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_add_storage_compression_pg">
+ <table name="t" start-index="12" stop-index="12" />
+ <add-column>
+ <column-definition type="text" start-index="25" stop-index="60"
not-null="false">
+ <column name="f" />
+ <data-type value="text" start-index="27" stop-index="30" />
+ </column-definition>
+ </add-column>
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_add_not_null_constraint_pg"
add-constraint-count="1">
+ <table name="t" start-index="12" stop-index="12" />
+ <add-constraint primary-key-column-count="0" start-index="18"
stop-index="27" />
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_add_not_null_no_inherit_pg"
add-constraint-count="1">
+ <table name="t" start-index="12" stop-index="12" />
+ <add-constraint primary-key-column-count="0" start-index="18"
stop-index="52" constraint-name="nn" />
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_add_not_null_validation_pg"
add-constraint-count="1">
+ <table name="t" start-index="12" stop-index="12" />
+ <add-constraint primary-key-column-count="0" start-index="18"
stop-index="62" constraint-name="nn" />
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_modify_constraint_validation_pg"
add-constraint-count="0">
+ <table name="t" start-index="12" stop-index="12" />
+ <modify-constraint constraint-name="nn" start-index="14"
stop-index="42" />
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_modify_constraint_no_inherit_pg"
add-constraint-count="0">
+ <table name="t" start-index="12" stop-index="12" />
+ <modify-constraint constraint-name="nn" start-index="14"
stop-index="43" />
+ </alter-table>
+
+ <alter-table sql-case-id="alter_table_modify_constraint_inherit_pg"
add-constraint-count="0">
+ <table name="t" start-index="12" stop-index="12" />
+ <modify-constraint constraint-name="nn" start-index="14"
stop-index="40" />
+ </alter-table>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/ddl/create-index.xml
b/test/it/parser/src/main/resources/case/ddl/create-index.xml
index 5e13abab80d..99974c9461d 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-index.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-index.xml
@@ -550,4 +550,20 @@
<index name="foo_idx" start-index="13" stop-index="19" />
<column name="a" start-index="39" stop-index="39" />
</create-index>
+
+ <create-index sql-case-id="create_index_nulls_distinct_pg">
+ <table>
+ <simple-table name="foo_table" start-index="27" stop-index="35" />
+ </table>
+ <index name="idx" start-index="20" stop-index="22" />
+ <column name="a" start-index="38" stop-index="38" />
+ </create-index>
+
+ <create-index sql-case-id="create_index_nulls_not_distinct_pg">
+ <table>
+ <simple-table name="foo_table" start-index="27" stop-index="35" />
+ </table>
+ <index name="idx" start-index="20" stop-index="22" />
+ <column name="a" start-index="38" stop-index="38" />
+ </create-index>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/ddl/create-table.xml
b/test/it/parser/src/main/resources/case/ddl/create-table.xml
index b6a12c309c7..9f77ec7b8bc 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-table.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-table.xml
@@ -5871,4 +5871,296 @@
<column name="1a20" />
</column-definition>
</create-table>
+
+ <create-table sql-case-id="create_table_virtual_column_pg">
+ <table name="foo_table" start-index="13" stop-index="21" />
+ <column-definition type="int" start-index="24" stop-index="28">
+ <column name="a" />
+ </column-definition>
+ <column-definition type="int" start-index="31" stop-index="71">
+ <column name="b" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_generated_default_pg">
+ <table name="foo_table" start-index="13" stop-index="21" />
+ <column-definition type="int" start-index="24" stop-index="28">
+ <column name="a" />
+ </column-definition>
+ <column-definition type="int" start-index="31" stop-index="63">
+ <column name="b" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_stored_column_pg">
+ <table name="foo_table" start-index="13" stop-index="21" />
+ <column-definition type="int" start-index="24" stop-index="28">
+ <column name="a" />
+ </column-definition>
+ <column-definition type="int" start-index="31" stop-index="70">
+ <column name="b" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_virtual_identifier_pg">
+ <table name="virtual" start-index="13" stop-index="19" />
+ <column-definition type="int" start-index="22" stop-index="32">
+ <column name="virtual" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_column_nulls_distinct_pg">
+ <table name="foo_table" start-index="13" stop-index="21" />
+ <column-definition type="int" not-null="false" start-index="24"
stop-index="50">
+ <column name="a" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_column_nulls_not_distinct_pg">
+ <table name="foo_table" start-index="13" stop-index="21" />
+ <column-definition type="int" not-null="false" start-index="24"
stop-index="54">
+ <column name="a" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_unique_nulls_distinct_pg">
+ <table name="foo_table" start-index="13" stop-index="21" />
+ <column-definition type="int" not-null="false" start-index="24"
stop-index="28">
+ <column name="a" />
+ </column-definition>
+ <constraint-definition constraint-name="foo_unique" start-index="31"
stop-index="77" />
+ </create-table>
+
+ <create-table sql-case-id="create_table_unique_nulls_not_distinct_pg">
+ <table name="foo_table" start-index="13" stop-index="21" />
+ <column-definition type="int" not-null="false" start-index="24"
stop-index="28">
+ <column name="a" />
+ </column-definition>
+ <constraint-definition constraint-name="foo_unique" start-index="31"
stop-index="81" />
+ </create-table>
+
+ <create-table sql-case-id="create_table_column_not_enforced_pg">
+ <table name="foo_table" start-index="13" stop-index="21" />
+ <column-definition type="int" not-null="true" start-index="24"
stop-index="84">
+ <column name="a" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_constraint_enforced_pg">
+ <table name="foo_table" start-index="13" stop-index="21" />
+ <column-definition type="int" not-null="false" start-index="24"
stop-index="28">
+ <column name="a" />
+ </column-definition>
+ <constraint-definition constraint-name="positive" start-index="31"
stop-index="72" />
+ </create-table>
+
+ <create-table sql-case-id="create_table_repeated_enforced_pg">
+ <table name="foo_table" start-index="13" stop-index="21" />
+ <column-definition type="int" not-null="false" start-index="24"
stop-index="64">
+ <column name="a" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_enforced_identifier_pg">
+ <table name="enforced" start-index="13" stop-index="20" />
+ <column-definition type="int" not-null="false" start-index="23"
stop-index="34">
+ <column name="enforced" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_named_check_priority_pg">
+ <table name="foo_table" start-index="13" stop-index="21" />
+ <column-definition type="int" not-null="false" start-index="24"
stop-index="28">
+ <column name="a" />
+ </column-definition>
+ <constraint-definition constraint-name="positive" start-index="31"
stop-index="63" />
+ </create-table>
+
+ <create-table sql-case-id="create_table_temporal_pk_pg">
+ <table name="foo_table" start-index="13" stop-index="21" />
+ <column-definition type="int4range" start-index="24" stop-index="35">
+ <column name="id" />
+ </column-definition>
+ <column-definition type="daterange" start-index="38" stop-index="55">
+ <column name="valid_at" />
+ </column-definition>
+ <constraint-definition primary-key-column-count="2"
constraint-name="foo_pk" start-index="58" stop-index="118">
+ <primary-key-column name="id" start-index="89" stop-index="90" />
+ <primary-key-column name="valid_at" start-index="93"
stop-index="100" />
+ </constraint-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_temporal_unique_pg">
+ <table name="foo_table" start-index="13" stop-index="21" />
+ <column-definition type="int4range" start-index="24" stop-index="35">
+ <column name="id" />
+ </column-definition>
+ <column-definition type="daterange" start-index="38" stop-index="55">
+ <column name="valid_at" />
+ </column-definition>
+ <constraint-definition primary-key-column-count="0"
constraint-name="foo_unique" start-index="58" stop-index="117" />
+ </create-table>
+
+ <create-table sql-case-id="create_table_temporal_inherited_pk_pg">
+ <table name="foo_table" start-index="13" stop-index="21" />
+ <constraint-definition primary-key-column-count="2"
constraint-name="foo_pk" start-index="24" stop-index="84">
+ <primary-key-column name="id" start-index="55" stop-index="56" />
+ <primary-key-column name="valid_at" start-index="59"
stop-index="66" />
+ </constraint-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_temporal_single_pk_pg">
+ <table name="foo_table" start-index="13" stop-index="21" />
+ <column-definition type="daterange" start-index="24" stop-index="41">
+ <column name="valid_at" />
+ </column-definition>
+ <constraint-definition primary-key-column-count="1" start-index="44"
stop-index="82">
+ <primary-key-column name="valid_at" start-index="57"
stop-index="64" />
+ </constraint-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_temporal_foreign_key_pg">
+ <table name="foo_table" start-index="13" stop-index="21" />
+ <column-definition type="int4range" start-index="24" stop-index="35">
+ <column name="id" />
+ </column-definition>
+ <column-definition type="daterange" start-index="38" stop-index="55">
+ <column name="valid_at" />
+ </column-definition>
+ <constraint-definition primary-key-column-count="0"
constraint-name="foo_fk" start-index="58" stop-index="154">
+ <referenced-table name="parent_table" start-index="121"
stop-index="132" />
+ </constraint-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_period_identifier_pg">
+ <table name="period" start-index="13" stop-index="18" />
+ <column-definition type="int" start-index="21" stop-index="30">
+ <column name="period" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_column_compression_pg">
+ <table name="t" start-index="13" stop-index="13" />
+ <column-definition type="text" start-index="16" stop-index="38"
not-null="false">
+ <column name="f" />
+ <data-type value="text" start-index="18" stop-index="21" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_column_compression_default_pg">
+ <table name="t" start-index="13" stop-index="13" />
+ <column-definition type="text" start-index="16" stop-index="41"
not-null="false">
+ <column name="f" />
+ <data-type value="text" start-index="18" stop-index="21" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_column_compression_not_null_pg">
+ <table name="t" start-index="13" stop-index="13" />
+ <column-definition type="text" start-index="16" stop-index="46"
not-null="true">
+ <column name="f" />
+ <data-type value="text" start-index="18" stop-index="21" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_column_compression_custom_pg">
+ <table name="t" start-index="13" stop-index="13" />
+ <column-definition type="text" start-index="16" stop-index="47"
not-null="false">
+ <column name="f" />
+ <data-type value="text" start-index="18" stop-index="21" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_column_storage_pg">
+ <table name="t" start-index="13" stop-index="13" />
+ <column-definition type="text" start-index="16" stop-index="38"
not-null="false">
+ <column name="f" />
+ <data-type value="text" start-index="18" stop-index="21" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_column_storage_compression_pg">
+ <table name="t" start-index="13" stop-index="13" />
+ <column-definition type="text" start-index="16" stop-index="57"
not-null="false">
+ <column name="f" />
+ <data-type value="text" start-index="18" stop-index="21" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_like_including_compression_pg">
+ <table name="t" start-index="13" stop-index="13" />
+ </create-table>
+
+ <create-table sql-case-id="create_table_like_excluding_compression_pg">
+ <table name="t" start-index="13" stop-index="13" />
+ </create-table>
+
+ <create-table sql-case-id="create_table_not_null_constraint_pg">
+ <table name="t" start-index="13" stop-index="13" />
+ <column-definition type="int" start-index="16" stop-index="20"
not-null="false">
+ <column name="a" />
+ <data-type value="int" start-index="18" stop-index="20" />
+ </column-definition>
+ <constraint-definition primary-key-column-count="0" start-index="23"
stop-index="32" />
+ </create-table>
+
+ <create-table sql-case-id="create_table_not_null_no_inherit_pg">
+ <table name="t" start-index="13" stop-index="13" />
+ <column-definition type="int" start-index="16" stop-index="20"
not-null="false">
+ <column name="a" />
+ <data-type value="int" start-index="18" stop-index="20" />
+ </column-definition>
+ <constraint-definition primary-key-column-count="0" start-index="23"
stop-index="57" constraint-name="nn" />
+ </create-table>
+
+ <create-table sql-case-id="create_table_column_not_null_inherit_pg">
+ <table name="t" start-index="13" stop-index="13" />
+ <column-definition type="int" start-index="16" stop-index="40"
not-null="true">
+ <column name="a" />
+ <data-type value="int" start-index="18" stop-index="20" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_column_named_not_null_inherit_pg">
+ <table name="t" start-index="13" stop-index="13" />
+ <column-definition type="int" start-index="16" stop-index="54"
not-null="true">
+ <column name="a" />
+ <data-type value="int" start-index="18" stop-index="20" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_column_check_inherit_control_pg">
+ <table name="t" start-index="13" stop-index="13" />
+ <column-definition type="int" start-index="16" stop-index="45"
not-null="false">
+ <column name="a" />
+ <data-type value="int" start-index="18" stop-index="20" />
+ </column-definition>
+ </create-table>
+
+ <create-table sql-case-id="create_table_check_inherit_control_pg">
+ <table name="t" start-index="13" stop-index="13" />
+ <column-definition type="int" start-index="16" stop-index="20"
not-null="false">
+ <column name="a" />
+ <data-type value="int" start-index="18" stop-index="20" />
+ </column-definition>
+ <constraint-definition primary-key-column-count="0" start-index="23"
stop-index="59" constraint-name="c" />
+ </create-table>
+
+ <create-table sql-case-id="create_table_check_validation_pg">
+ <table name="t" start-index="13" stop-index="13" />
+ <column-definition type="int" start-index="16" stop-index="20"
not-null="false">
+ <column name="a" />
+ <data-type value="int" start-index="18" stop-index="20" />
+ </column-definition>
+ <constraint-definition primary-key-column-count="0" start-index="23"
stop-index="69" constraint-name="c" />
+ </create-table>
+
+ <create-table sql-case-id="create_table_check_validation_order_pg">
+ <table name="t" start-index="13" stop-index="13" />
+ <column-definition type="int" start-index="16" stop-index="20"
not-null="false">
+ <column name="a" />
+ <data-type value="int" start-index="18" stop-index="20" />
+ </column-definition>
+ <constraint-definition primary-key-column-count="0" start-index="23"
stop-index="69" constraint-name="c" />
+ </create-table>
</sql-parser-test-cases>
diff --git a/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
b/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
index 89fa506e022..1cdb6355c5f 100644
--- a/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
+++ b/test/it/parser/src/main/resources/case/ddl/create-trigger.xml
@@ -105,6 +105,47 @@
<create-trigger sql-case-id="create_trigger_pg_constraint">
<trigger-name name="t_merge_constraint" start-index="26"
stop-index="43" />
<table name="t_merge_target" start-index="61" stop-index="74" />
+ <table name="t_merge_source" start-index="81" stop-index="94" />
</create-trigger>
<create-trigger sql-case-id="create_trigger_if_not_exists_mysql" />
+
+ <create-trigger sql-case-id="create_trigger_constraint_named_pg">
+ <trigger-name name="ct" start-index="26" stop-index="27" />
+ <table name="t" start-index="45" stop-index="45" />
+ </create-trigger>
+
+ <create-trigger sql-case-id="create_trigger_constraint_deferred_pg">
+ <trigger-name name="ct" start-index="26" stop-index="27" />
+ <table name="t" start-index="55" stop-index="57">
+ <owner name="s" start-index="55" stop-index="55" />
+ </table>
+ </create-trigger>
+
+ <create-trigger sql-case-id="create_trigger_constraint_from_pg">
+ <trigger-name name="ct" start-index="26" stop-index="27" />
+ <table name="t" start-index="45" stop-index="45" />
+ <table name="ref" start-index="52" stop-index="56">
+ <owner name="s" start-index="52" stop-index="52" />
+ </table>
+ </create-trigger>
+
+ <create-trigger sql-case-id="create_trigger_constraint_when_pg">
+ <trigger-name name="ct" start-index="26" stop-index="27" />
+ <table name="t" start-index="45" stop-index="45" />
+ </create-trigger>
+
+ <create-trigger sql-case-id="create_trigger_replace_regular_pg">
+ <trigger-name name="tr" start-index="26" stop-index="27" />
+ <table name="t" start-index="45" stop-index="45" />
+ </create-trigger>
+
+ <create-trigger sql-case-id="create_trigger_regular_control_pg">
+ <trigger-name name="tr" start-index="15" stop-index="16" />
+ <table name="t" start-index="34" stop-index="34" />
+ </create-trigger>
+
+ <create-trigger sql-case-id="create_trigger_pg_constraint_without_from">
+ <trigger-name name="t_constraint" start-index="26" stop-index="37" />
+ <table name="t_merge_target" start-index="55" stop-index="68" />
+ </create-trigger>
</sql-parser-test-cases>
diff --git
a/test/it/parser/src/main/resources/case/ddl/postgresql-regression.xml
b/test/it/parser/src/main/resources/case/ddl/postgresql-regression.xml
index 81132f6c724..b6c37b3421e 100644
--- a/test/it/parser/src/main/resources/case/ddl/postgresql-regression.xml
+++ b/test/it/parser/src/main/resources/case/ddl/postgresql-regression.xml
@@ -146,6 +146,7 @@
<create-trigger sql-case-id="create_constraint_trigger_postgresql">
<trigger-name name="foo_trigger" start-index="26" stop-index="36" />
<table name="foo" start-index="54" stop-index="56" />
+ <table name="bar" start-index="63" stop-index="65" />
</create-trigger>
<create-trigger
sql-case-id="create_constraint_trigger_optional_clauses_postgresql">
<trigger-name name="foo_trigger" start-index="26" stop-index="36" />
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/alter-table.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/alter-table.xml
index 26cb4e506fd..888baa47669 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/alter-table.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/alter-table.xml
@@ -498,4 +498,23 @@
<sql-case id="alter_table_reset_set_control_pg" value="ALTER TABLE t SET
(fillfactor = 70)" db-types="PostgreSQL" />
<sql-case id="alter_table_reset_value_control_pg" value="ALTER TABLE t
RESET (fillfactor = 70)" db-types="PostgreSQL" />
<sql-case id="alter_table_column_reset_value_control_pg" value="ALTER
TABLE t ALTER COLUMN c RESET (n_distinct = 0.1)" db-types="PostgreSQL" />
+ <sql-case id="alter_table_add_not_enforced_pg" value="ALTER TABLE
foo_table ADD CONSTRAINT positive CHECK (a > 0) NOT ENFORCED NOT VALID"
db-types="PostgreSQL" case-types="Literal" />
+ <sql-case id="alter_table_constraint_enforced_pg" value="ALTER TABLE
foo_table ALTER CONSTRAINT positive ENFORCED" db-types="PostgreSQL"
case-types="Literal" />
+ <sql-case id="alter_table_constraint_attribute_order_pg" value="ALTER
TABLE foo_table ALTER CONSTRAINT positive INITIALLY DEFERRED DEFERRABLE"
db-types="PostgreSQL" case-types="Literal" />
+ <sql-case id="alter_table_temporal_pk_pg" value="ALTER TABLE foo_table ADD
CONSTRAINT foo_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)"
db-types="PostgreSQL" case-types="Literal" />
+ <sql-case id="alter_table_temporal_unique_pg" value="ALTER TABLE foo_table
ADD CONSTRAINT foo_unique UNIQUE (id, valid_at WITHOUT OVERLAPS)"
db-types="PostgreSQL" case-types="Literal" />
+ <sql-case id="alter_table_temporal_foreign_key_pg" value="ALTER TABLE
foo_table ADD CONSTRAINT foo_fk FOREIGN KEY (id, PERIOD valid_at) REFERENCES
parent_table (id, PERIOD valid_at)" db-types="PostgreSQL" case-types="Literal"
/>
+ <sql-case id="alter_table_set_expression_pg" value="ALTER TABLE t ALTER
COLUMN b SET EXPRESSION AS (a * 100)" db-types="PostgreSQL" />
+ <sql-case id="alter_table_set_expression_without_column_pg" value="ALTER
TABLE t ALTER b SET EXPRESSION AS (a * 3)" db-types="PostgreSQL" />
+ <sql-case id="alter_table_set_expression_qualified_pg" value="ALTER TABLE
ONLY s.t ALTER COLUMN "b" SET EXPRESSION AS (NULL::int)"
db-types="PostgreSQL" />
+ <sql-case id="alter_table_set_expression_function_pg" value="ALTER TABLE t
ALTER COLUMN b SET EXPRESSION AS (nullif(a, 1) + nullif(c, 2))"
db-types="PostgreSQL" />
+ <sql-case id="alter_table_set_expression_multiple_pg" value="ALTER TABLE t
ALTER COLUMN b SET EXPRESSION AS (a * 2), ALTER COLUMN c SET EXPRESSION AS (a *
3)" db-types="PostgreSQL" />
+ <sql-case id="alter_table_set_expression_default_control_pg" value="ALTER
TABLE t ALTER COLUMN b SET DEFAULT (a * 2)" db-types="PostgreSQL" />
+ <sql-case id="alter_table_add_storage_compression_pg" value="ALTER TABLE t
ADD COLUMN f text STORAGE MAIN COMPRESSION pglz" db-types="PostgreSQL" />
+ <sql-case id="alter_table_add_not_null_constraint_pg" value="ALTER TABLE t
ADD NOT NULL a" db-types="PostgreSQL" />
+ <sql-case id="alter_table_add_not_null_no_inherit_pg" value="ALTER TABLE t
ADD CONSTRAINT nn NOT NULL a NO INHERIT" db-types="PostgreSQL" />
+ <sql-case id="alter_table_add_not_null_validation_pg" value="ALTER TABLE t
ADD CONSTRAINT nn NOT NULL a NOT VALID NO INHERIT" db-types="PostgreSQL" />
+ <sql-case id="alter_table_modify_constraint_validation_pg" value="ALTER
TABLE t ALTER CONSTRAINT nn NOT VALID" db-types="PostgreSQL" />
+ <sql-case id="alter_table_modify_constraint_no_inherit_pg" value="ALTER
TABLE t ALTER CONSTRAINT nn NO INHERIT" db-types="PostgreSQL" />
+ <sql-case id="alter_table_modify_constraint_inherit_pg" value="ALTER TABLE
t ALTER CONSTRAINT nn INHERIT" db-types="PostgreSQL" />
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-index.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-index.xml
index b2c16dedfe1..587127543bf 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-index.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-index.xml
@@ -109,4 +109,6 @@
<sql-case id="create_index_cast_binary_pg" value="CREATE INDEX foo_idx ON
foo_table (CAST(a + CAST(b AS integer) AS integer))" db-types="PostgreSQL"
case-types="Literal" />
<sql-case id="create_index_nested_function_cast_pg" value="CREATE INDEX
foo_idx ON foo_table (coalesce(abs(CAST(b AS integer)), CAST(a AS integer),
b))" db-types="PostgreSQL" case-types="Literal" />
<sql-case id="create_index_array_subscript_function_pg" value="CREATE
INDEX foo_idx ON foo_table (abs(a[1]))" db-types="PostgreSQL"
case-types="Literal" />
+ <sql-case id="create_index_nulls_distinct_pg" value="CREATE UNIQUE INDEX
idx ON foo_table (a) INCLUDE (b) NULLS DISTINCT" db-types="PostgreSQL" />
+ <sql-case id="create_index_nulls_not_distinct_pg" value="CREATE UNIQUE
INDEX idx ON foo_table (a) NULLS NOT DISTINCT" db-types="PostgreSQL" />
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
index ddb4b948d7b..fca23f5aa89 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-table.xml
@@ -807,4 +807,74 @@ WHERE time_id >= TO_DATE('01-JAN-1998','dd-mon-yyyy')
<sql-case id="create_table_digit_identifiers" value="CREATE TABLE db.1a20
(1a20 INT)" db-types="MySQL,Doris" />
+
+ <sql-case id="create_table_virtual_column_pg" value="CREATE TABLE
foo_table (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL)"
db-types="PostgreSQL" />
+
+ <sql-case id="create_table_generated_default_pg" value="CREATE TABLE
foo_table (a int, b int GENERATED ALWAYS AS (a * 2))" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_stored_column_pg" value="CREATE TABLE foo_table
(a int, b int GENERATED ALWAYS AS (a * 2) STORED)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_virtual_identifier_pg" value="CREATE TABLE
virtual (virtual int)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_column_nulls_distinct_pg" value="CREATE TABLE
foo_table (a int UNIQUE NULLS DISTINCT)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_column_nulls_not_distinct_pg" value="CREATE
TABLE foo_table (a int UNIQUE NULLS NOT DISTINCT)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_unique_nulls_distinct_pg" value="CREATE TABLE
foo_table (a int, CONSTRAINT foo_unique UNIQUE NULLS DISTINCT (a))"
db-types="PostgreSQL" />
+
+ <sql-case id="create_table_unique_nulls_not_distinct_pg" value="CREATE
TABLE foo_table (a int, CONSTRAINT foo_unique UNIQUE NULLS NOT DISTINCT (a))"
db-types="PostgreSQL" />
+
+ <sql-case id="create_table_column_not_enforced_pg" value="CREATE TABLE
foo_table (a int NOT NULL CONSTRAINT positive CHECK (a > 0) NOT ENFORCED)"
db-types="PostgreSQL" case-types="Literal" />
+
+ <sql-case id="create_table_constraint_enforced_pg" value="CREATE TABLE
foo_table (a int, CONSTRAINT positive CHECK (a > 0) ENFORCED)"
db-types="PostgreSQL" case-types="Literal" />
+
+ <sql-case id="create_table_repeated_enforced_pg" value="CREATE TABLE
foo_table (a int CHECK (a > 0) NOT ENFORCED ENFORCED)" db-types="PostgreSQL"
case-types="Literal" />
+
+ <sql-case id="create_table_enforced_identifier_pg" value="CREATE TABLE
enforced (enforced int)" db-types="PostgreSQL" case-types="Literal" />
+
+ <sql-case id="create_table_named_check_priority_pg" value="CREATE TABLE
foo_table (a int, CONSTRAINT positive CHECK (a > 0))" db-types="PostgreSQL"
case-types="Literal" />
+
+ <sql-case id="create_table_temporal_pk_pg" value="CREATE TABLE foo_table
(id int4range, valid_at daterange, CONSTRAINT foo_pk PRIMARY KEY (id, valid_at
WITHOUT OVERLAPS))" db-types="PostgreSQL" case-types="Literal" />
+
+ <sql-case id="create_table_temporal_unique_pg" value="CREATE TABLE
foo_table (id int4range, valid_at daterange, CONSTRAINT foo_unique UNIQUE (id,
valid_at WITHOUT OVERLAPS))" db-types="PostgreSQL" case-types="Literal" />
+
+ <sql-case id="create_table_temporal_inherited_pk_pg" value="CREATE TABLE
foo_table (CONSTRAINT foo_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS))
INHERITS (parent_table)" db-types="PostgreSQL" case-types="Literal" />
+
+ <sql-case id="create_table_temporal_single_pk_pg" value="CREATE TABLE
foo_table (valid_at daterange, PRIMARY KEY (valid_at WITHOUT OVERLAPS))"
db-types="PostgreSQL" case-types="Literal" />
+
+ <sql-case id="create_table_temporal_foreign_key_pg" value="CREATE TABLE
foo_table (id int4range, valid_at daterange, CONSTRAINT foo_fk FOREIGN KEY (id,
PERIOD valid_at) REFERENCES parent_table (id, PERIOD valid_at))"
db-types="PostgreSQL" case-types="Literal" />
+
+ <sql-case id="create_table_period_identifier_pg" value="CREATE TABLE
period (period int)" db-types="PostgreSQL" case-types="Literal" />
+
+ <sql-case id="create_table_column_compression_pg" value="CREATE TABLE t (f
text COMPRESSION pglz)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_column_compression_default_pg" value="CREATE
TABLE t (f text COMPRESSION DEFAULT)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_column_compression_not_null_pg" value="CREATE
TABLE t (f text COMPRESSION lz4 NOT NULL)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_column_compression_custom_pg" value="CREATE
TABLE t (f text COMPRESSION custom_method)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_column_storage_pg" value="CREATE TABLE t (f
text STORAGE EXTERNAL)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_column_storage_compression_pg" value="CREATE
TABLE t (f text STORAGE DEFAULT COMPRESSION DEFAULT)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_like_including_compression_pg" value="CREATE
TABLE t (LIKE src INCLUDING COMPRESSION)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_like_excluding_compression_pg" value="CREATE
TABLE t (LIKE src EXCLUDING COMPRESSION INCLUDING STORAGE)"
db-types="PostgreSQL" />
+
+ <sql-case id="create_table_not_null_constraint_pg" value="CREATE TABLE t
(a int, NOT NULL a)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_not_null_no_inherit_pg" value="CREATE TABLE t
(a int, CONSTRAINT nn NOT NULL a NO INHERIT)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_column_not_null_inherit_pg" value="CREATE TABLE
t (a int NOT NULL NO INHERIT)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_column_named_not_null_inherit_pg" value="CREATE
TABLE t (a int CONSTRAINT nn NOT NULL NO INHERIT)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_column_check_inherit_control_pg" value="CREATE
TABLE t (a int CHECK (a > 0) NO INHERIT)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_check_inherit_control_pg" value="CREATE TABLE t
(a int, CONSTRAINT c CHECK (a > 0) NO INHERIT)" db-types="PostgreSQL" />
+
+ <sql-case id="create_table_check_validation_pg" value="CREATE TABLE t (a
int, CONSTRAINT c CHECK (a > 0) NO INHERIT NOT VALID)" db-types="PostgreSQL"
/>
+
+ <sql-case id="create_table_check_validation_order_pg" value="CREATE TABLE
t (a int, CONSTRAINT c CHECK (a > 0) NOT VALID NO INHERIT)"
db-types="PostgreSQL" />
</sql-cases>
diff --git
a/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
b/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
index 2f29af03794..cf3e4c8f0cc 100644
--- a/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
+++ b/test/it/parser/src/main/resources/sql/supported/ddl/create-trigger.xml
@@ -138,4 +138,11 @@ END;" db-types="Oracle" />
<sql-case id="create_trigger_pg_multi_events_when" value="CREATE TRIGGER
check_update BEFORE INSERT OR DELETE ON t_merge_target FOR EACH ROW WHEN
(order_id = 1) EXECUTE PROCEDURE process_order_audit()" db-types="PostgreSQL" />
<sql-case id="create_trigger_pg_constraint" value="CREATE CONSTRAINT
TRIGGER t_merge_constraint AFTER INSERT ON t_merge_target FROM t_merge_source
DEFERRABLE FOR EACH ROW WHEN (NEW.order_id = 1) EXECUTE FUNCTION
merge_notify()" db-types="PostgreSQL" />
<sql-case id="create_trigger_if_not_exists_mysql" value="CREATE TRIGGER IF
NOT EXISTS tr BEFORE INSERT ON t FOR EACH ROW SET @a=1" db-types="MySQL,Doris"
/>
+ <sql-case id="create_trigger_constraint_named_pg" value="CREATE CONSTRAINT
TRIGGER ct AFTER INSERT ON t FOR EACH ROW EXECUTE FUNCTION f()"
db-types="PostgreSQL" />
+ <sql-case id="create_trigger_constraint_deferred_pg" value="CREATE
CONSTRAINT TRIGGER ct AFTER UPDATE OR DELETE ON s.t DEFERRABLE INITIALLY
DEFERRED FOR EACH ROW EXECUTE PROCEDURE s.f()" db-types="PostgreSQL" />
+ <sql-case id="create_trigger_constraint_from_pg" value="CREATE CONSTRAINT
TRIGGER ct AFTER INSERT ON t FROM s.ref FOR EACH ROW EXECUTE FUNCTION f()"
db-types="PostgreSQL" />
+ <sql-case id="create_trigger_constraint_when_pg" value="CREATE CONSTRAINT
TRIGGER ct AFTER UPDATE ON t FOR EACH ROW WHEN (NEW.a > 0) EXECUTE FUNCTION
f()" db-types="PostgreSQL" />
+ <sql-case id="create_trigger_replace_regular_pg" value="CREATE OR REPLACE
TRIGGER tr AFTER INSERT ON t FOR EACH ROW EXECUTE FUNCTION f()"
db-types="PostgreSQL" />
+ <sql-case id="create_trigger_regular_control_pg" value="CREATE TRIGGER tr
AFTER INSERT ON t FOR EACH ROW EXECUTE FUNCTION f()" db-types="PostgreSQL" />
+ <sql-case id="create_trigger_pg_constraint_without_from" value="CREATE
CONSTRAINT TRIGGER t_constraint AFTER INSERT ON t_merge_target DEFERRABLE FOR
EACH ROW WHEN (order_id = 1) EXECUTE FUNCTION merge_notify()"
db-types="PostgreSQL" />
</sql-cases>
