PostgreSQL: support INCLUDE on PRIMARY KEY / UNIQUE table constraints (#2366)
diff --git a/src/ast/table_constraints.rs b/src/ast/table_constraints.rs
index 463bf43..0db71bb 100644
--- a/src/ast/table_constraints.rs
+++ b/src/ast/table_constraints.rs
@@ -456,6 +456,8 @@
pub index_type: Option<IndexType>,
/// Identifiers of the columns that form the primary key.
pub columns: Vec<IndexColumn>,
+ /// INCLUDE clause: <https://www.postgresql.org/docs/current/sql-createtable.html>
+ pub include: Vec<Ident>,
/// Optional index options such as `USING`.
pub index_options: Vec<IndexOption>,
/// Optional characteristics like `DEFERRABLE`.
@@ -474,6 +476,10 @@
display_comma_separated(&self.columns),
)?;
+ if !self.include.is_empty() {
+ write!(f, " INCLUDE ({})", display_comma_separated(&self.include))?;
+ }
+
if !self.index_options.is_empty() {
write!(f, " {}", display_separated(&self.index_options, " "))?;
}
@@ -495,6 +501,7 @@
.map(|i| i.span)
.chain(self.index_name.iter().map(|i| i.span))
.chain(self.columns.iter().map(|i| i.span()))
+ .chain(self.include.iter().map(|i| i.span))
.chain(self.characteristics.iter().map(|i| i.span())),
)
}
@@ -519,6 +526,8 @@
pub index_type: Option<IndexType>,
/// Identifiers of the columns that are unique.
pub columns: Vec<IndexColumn>,
+ /// INCLUDE clause: <https://www.postgresql.org/docs/current/sql-createtable.html>
+ pub include: Vec<Ident>,
/// Optional index options such as `USING`.
pub index_options: Vec<IndexOption>,
/// Optional characteristics like `DEFERRABLE`.
@@ -541,6 +550,10 @@
display_comma_separated(&self.columns),
)?;
+ if !self.include.is_empty() {
+ write!(f, " INCLUDE ({})", display_comma_separated(&self.include))?;
+ }
+
if !self.index_options.is_empty() {
write!(f, " {}", display_separated(&self.index_options, " "))?;
}
@@ -562,6 +575,7 @@
.map(|i| i.span)
.chain(self.index_name.iter().map(|i| i.span))
.chain(self.columns.iter().map(|i| i.span()))
+ .chain(self.include.iter().map(|i| i.span))
.chain(self.characteristics.iter().map(|i| i.span())),
)
}
diff --git a/src/parser/mod.rs b/src/parser/mod.rs
index 74199c2..2d51d47 100644
--- a/src/parser/mod.rs
+++ b/src/parser/mod.rs
@@ -9581,6 +9581,7 @@
index_name: None,
index_type: None,
columns: vec![],
+ include: vec![],
index_options: vec![],
characteristics,
}
@@ -9601,6 +9602,7 @@
index_type_display,
index_type: None,
columns: vec![],
+ include: vec![],
index_options: vec![],
characteristics,
nulls_distinct: NullsDistinctOption::None,
@@ -9617,6 +9619,7 @@
index_name: None,
index_type: None,
columns: vec![],
+ include: vec![],
index_options: vec![],
characteristics,
}
@@ -10044,6 +10047,7 @@
let index_type = self.parse_optional_using_then_index_type()?;
let columns = self.parse_parenthesized_index_column_list()?;
+ let include = self.parse_optional_include_columns()?;
let index_options = self.parse_index_options()?;
let characteristics = self.parse_constraint_characteristics()?;
Ok(Some(
@@ -10053,6 +10057,7 @@
index_type_display,
index_type,
columns,
+ include,
index_options,
characteristics,
nulls_distinct,
@@ -10077,6 +10082,7 @@
let index_type = self.parse_optional_using_then_index_type()?;
let columns = self.parse_parenthesized_index_column_list()?;
+ let include = self.parse_optional_include_columns()?;
let index_options = self.parse_index_options()?;
let characteristics = self.parse_constraint_characteristics()?;
Ok(Some(
@@ -10085,6 +10091,7 @@
index_name,
index_type,
columns,
+ include,
index_options,
characteristics,
}
@@ -10469,6 +10476,18 @@
}
}
+ /// Parse an optional `INCLUDE (col, ...)` clause on a table constraint.
+ pub fn parse_optional_include_columns(&mut self) -> Result<Vec<Ident>, ParserError> {
+ if self.parse_keyword(Keyword::INCLUDE) {
+ self.expect_token(&Token::LParen)?;
+ let columns = self.parse_comma_separated(|p| p.parse_identifier())?;
+ self.expect_token(&Token::RParen)?;
+ Ok(columns)
+ } else {
+ Ok(vec![])
+ }
+ }
+
/// Parse a single `SqlOption` used by various dialect-specific DDL statements.
pub fn parse_sql_option(&mut self) -> Result<SqlOption, ParserError> {
let is_mssql = dialect_of!(self is MsSqlDialect|GenericDialect);
diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs
index 97f5a04..bad0836 100644
--- a/tests/sqlparser_common.rs
+++ b/tests/sqlparser_common.rs
@@ -3955,6 +3955,7 @@
index_name: None,
index_type: None,
columns: vec![],
+ include: vec![],
index_options: vec![],
characteristics: None,
}),
@@ -3971,6 +3972,7 @@
index_type_display: KeyOrIndexDisplay::None,
index_type: None,
columns: vec![],
+ include: vec![],
index_options: vec![],
characteristics: None,
nulls_distinct: NullsDistinctOption::None,
@@ -4309,6 +4311,7 @@
index_type_display: KeyOrIndexDisplay::None,
index_type: None,
columns: vec![],
+ include: vec![],
index_options: vec![],
characteristics: expected_value,
nulls_distinct: NullsDistinctOption::None,
diff --git a/tests/sqlparser_mysql.rs b/tests/sqlparser_mysql.rs
index d5b3a74..797a125 100644
--- a/tests/sqlparser_mysql.rs
+++ b/tests/sqlparser_mysql.rs
@@ -655,6 +655,7 @@
index_name: None,
index_type: None,
columns: vec![],
+ include: vec![],
index_options: vec![],
characteristics: None,
}),
@@ -706,6 +707,7 @@
index_type_display,
index_type,
columns,
+ include: vec![],
index_options,
characteristics,
nulls_distinct: NullsDistinctOption::None,
@@ -716,6 +718,7 @@
index_name,
index_type,
columns,
+ include: vec![],
index_options,
characteristics,
}
@@ -764,6 +767,7 @@
index_name: None,
index_type: None,
columns: vec![],
+ include: vec![],
index_options: vec![],
characteristics: None,
}),
@@ -1436,6 +1440,7 @@
index_name: None,
index_type: None,
columns: vec![],
+ include: vec![],
index_options: vec![],
characteristics: None,
}),
diff --git a/tests/sqlparser_postgres.rs b/tests/sqlparser_postgres.rs
index 73cb9ec..a7128ea 100644
--- a/tests/sqlparser_postgres.rs
+++ b/tests/sqlparser_postgres.rs
@@ -792,6 +792,50 @@
}
#[test]
+fn parse_constraint_include_columns() {
+ // INCLUDE covering columns on PRIMARY KEY / UNIQUE table constraints.
+ // https://www.postgresql.org/docs/current/sql-createtable.html
+ pg_and_generic().verified_stmt(
+ "CREATE TABLE t (id INT, payload TEXT, CONSTRAINT t_pk PRIMARY KEY (id) INCLUDE (payload))",
+ );
+ pg_and_generic().verified_stmt(
+ "CREATE TABLE t (id INT, email TEXT, payload TEXT, CONSTRAINT t_uk UNIQUE (email) INCLUDE (payload))",
+ );
+ pg_and_generic().verified_stmt(
+ "CREATE TABLE t (a INT, b INT, c INT, d INT, CONSTRAINT t_pk PRIMARY KEY (a, b) INCLUDE (c, d))",
+ );
+ pg_and_generic()
+ .verified_stmt("ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id) INCLUDE (payload)");
+ pg_and_generic()
+ .verified_stmt("ALTER TABLE t ADD CONSTRAINT t_uk UNIQUE (email) INCLUDE (payload)");
+ pg_and_generic().verified_stmt(
+ "ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id) INCLUDE (payload) DEFERRABLE INITIALLY DEFERRED",
+ );
+
+ match pg_and_generic().verified_stmt(
+ "ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id) INCLUDE (payload, extra)",
+ ) {
+ Statement::AlterTable(alter_table) => match &alter_table.operations[0] {
+ AlterTableOperation::AddConstraint {
+ constraint: TableConstraint::PrimaryKey(pk),
+ ..
+ } => {
+ assert_eq!(pk.name.as_ref().unwrap().to_string(), "t_pk");
+ assert_eq!(
+ pk.include
+ .iter()
+ .map(|i| i.value.clone())
+ .collect::<Vec<_>>(),
+ vec!["payload".to_string(), "extra".to_string()]
+ );
+ }
+ _ => unreachable!(),
+ },
+ _ => unreachable!(),
+ }
+}
+
+#[test]
fn parse_alter_table_disable() {
pg_and_generic().verified_stmt("ALTER TABLE tab DISABLE ROW LEVEL SECURITY");
pg_and_generic().verified_stmt("ALTER TABLE tab DISABLE RULE rule_name");
diff --git a/tests/sqlparser_sqlite.rs b/tests/sqlparser_sqlite.rs
index 70c73bf..b72d294 100644
--- a/tests/sqlparser_sqlite.rs
+++ b/tests/sqlparser_sqlite.rs
@@ -223,6 +223,7 @@
index_name: None,
index_type: None,
columns: vec![],
+ include: vec![],
index_options: vec![],
characteristics: None,
}),
@@ -255,6 +256,7 @@
index_name: None,
index_type: None,
columns: vec![],
+ include: vec![],
index_options: vec![],
characteristics: None,
}),
