This is an automated email from the ASF dual-hosted git repository.
iffyio pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion-sqlparser-rs.git
The following commit(s) were added to refs/heads/main by this push:
new c1648e79 Postgres: Support `INTERVAL` data type options (#1984)
c1648e79 is described below
commit c1648e79fe1452a6e574c88bd089f3224f012c8c
Author: Michael Victor Zink <michae...@readyset.io>
AuthorDate: Fri Aug 1 23:05:13 2025 -0700
Postgres: Support `INTERVAL` data type options (#1984)
---
src/ast/data_type.rs | 64 +++++++++++++++++++++++++++++-
src/ast/mod.rs | 2 +-
src/dialect/generic.rs | 4 ++
src/dialect/mod.rs | 15 +++++++
src/dialect/postgresql.rs | 7 ++++
src/parser/mod.rs | 97 ++++++++++++++++++++++++++++++++++++++++++---
tests/sqlparser_bigquery.rs | 10 ++++-
tests/sqlparser_common.rs | 15 +++++--
tests/sqlparser_postgres.rs | 38 ++++++++++++++++++
9 files changed, 239 insertions(+), 13 deletions(-)
diff --git a/src/ast/data_type.rs b/src/ast/data_type.rs
index 0897f2db..b4a8af60 100644
--- a/src/ast/data_type.rs
+++ b/src/ast/data_type.rs
@@ -346,7 +346,16 @@ pub enum DataType {
/// [1]:
https://docs.databricks.com/aws/en/sql/language-manual/data-types/timestamp-ntz-type
TimestampNtz,
/// Interval type.
- Interval,
+ Interval {
+ /// [PostgreSQL] fields specification like `INTERVAL YEAR TO MONTH`.
+ ///
+ /// [PostgreSQL]:
https://www.postgresql.org/docs/17/datatype-datetime.html
+ fields: Option<IntervalFields>,
+ /// [PostgreSQL] subsecond precision like `INTERVAL HOUR TO SECOND(3)`
+ ///
+ /// [PostgreSQL]:
https://www.postgresql.org/docs/17/datatype-datetime.html
+ precision: Option<u64>,
+ },
/// JSON type.
JSON,
/// Binary JSON type.
@@ -635,7 +644,16 @@ impl fmt::Display for DataType {
timezone,
)
}
- DataType::Interval => write!(f, "INTERVAL"),
+ DataType::Interval { fields, precision } => {
+ write!(f, "INTERVAL")?;
+ if let Some(fields) = fields {
+ write!(f, " {fields}")?;
+ }
+ if let Some(precision) = precision {
+ write!(f, "({precision})")?;
+ }
+ Ok(())
+ }
DataType::JSON => write!(f, "JSON"),
DataType::JSONB => write!(f, "JSONB"),
DataType::Regclass => write!(f, "REGCLASS"),
@@ -889,6 +907,48 @@ impl fmt::Display for TimezoneInfo {
}
}
+/// Fields for [Postgres] `INTERVAL` type.
+///
+/// [Postgres]: https://www.postgresql.org/docs/17/datatype-datetime.html
+#[derive(Debug, Copy, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
+#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
+#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
+pub enum IntervalFields {
+ Year,
+ Month,
+ Day,
+ Hour,
+ Minute,
+ Second,
+ YearToMonth,
+ DayToHour,
+ DayToMinute,
+ DayToSecond,
+ HourToMinute,
+ HourToSecond,
+ MinuteToSecond,
+}
+
+impl fmt::Display for IntervalFields {
+ fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
+ match self {
+ IntervalFields::Year => write!(f, "YEAR"),
+ IntervalFields::Month => write!(f, "MONTH"),
+ IntervalFields::Day => write!(f, "DAY"),
+ IntervalFields::Hour => write!(f, "HOUR"),
+ IntervalFields::Minute => write!(f, "MINUTE"),
+ IntervalFields::Second => write!(f, "SECOND"),
+ IntervalFields::YearToMonth => write!(f, "YEAR TO MONTH"),
+ IntervalFields::DayToHour => write!(f, "DAY TO HOUR"),
+ IntervalFields::DayToMinute => write!(f, "DAY TO MINUTE"),
+ IntervalFields::DayToSecond => write!(f, "DAY TO SECOND"),
+ IntervalFields::HourToMinute => write!(f, "HOUR TO MINUTE"),
+ IntervalFields::HourToSecond => write!(f, "HOUR TO SECOND"),
+ IntervalFields::MinuteToSecond => write!(f, "MINUTE TO SECOND"),
+ }
+ }
+}
+
/// Additional information for `NUMERIC`, `DECIMAL`, and `DEC` data types
/// following the 2016 [SQL Standard].
///
diff --git a/src/ast/mod.rs b/src/ast/mod.rs
index 34112ac6..c6212f1e 100644
--- a/src/ast/mod.rs
+++ b/src/ast/mod.rs
@@ -52,7 +52,7 @@ use crate::{
pub use self::data_type::{
ArrayElemTypeDef, BinaryLength, CharLengthUnits, CharacterLength,
DataType, EnumMember,
- ExactNumberInfo, StructBracketKind, TimezoneInfo,
+ ExactNumberInfo, IntervalFields, StructBracketKind, TimezoneInfo,
};
pub use self::dcl::{
AlterRoleOperation, ResetConfig, RoleOption, SecondaryRoles,
SetConfigValue, Use,
diff --git a/src/dialect/generic.rs b/src/dialect/generic.rs
index de83d507..b4c3ef02 100644
--- a/src/dialect/generic.rs
+++ b/src/dialect/generic.rs
@@ -187,4 +187,8 @@ impl Dialect for GenericDialect {
fn supports_data_type_signed_suffix(&self) -> bool {
true
}
+
+ fn supports_interval_options(&self) -> bool {
+ true
+ }
}
diff --git a/src/dialect/mod.rs b/src/dialect/mod.rs
index 9003220c..5f333a93 100644
--- a/src/dialect/mod.rs
+++ b/src/dialect/mod.rs
@@ -1148,6 +1148,21 @@ pub trait Dialect: Debug + Any {
fn supports_data_type_signed_suffix(&self) -> bool {
false
}
+
+ /// Returns true if the dialect supports the `INTERVAL` data type with
[Postgres]-style options.
+ ///
+ /// Examples:
+ /// ```sql
+ /// CREATE TABLE t (i INTERVAL YEAR TO MONTH);
+ /// SELECT '1 second'::INTERVAL HOUR TO SECOND(3);
+ /// ```
+ ///
+ /// See [`crate::ast::DataType::Interval`] and
[`crate::ast::IntervalFields`].
+ ///
+ /// [Postgres]: https://www.postgresql.org/docs/17/datatype-datetime.html
+ fn supports_interval_options(&self) -> bool {
+ false
+ }
}
/// This represents the operators for which precedence must be defined
diff --git a/src/dialect/postgresql.rs b/src/dialect/postgresql.rs
index b12abaaf..207f4787 100644
--- a/src/dialect/postgresql.rs
+++ b/src/dialect/postgresql.rs
@@ -269,4 +269,11 @@ impl Dialect for PostgreSqlDialect {
fn supports_notnull_operator(&self) -> bool {
true
}
+
+ /// [Postgres] supports optional field and precision options for
`INTERVAL` data type.
+ ///
+ /// [Postgres]: https://www.postgresql.org/docs/17/datatype-datetime.html
+ fn supports_interval_options(&self) -> bool {
+ true
+ }
}
diff --git a/src/parser/mod.rs b/src/parser/mod.rs
index 455e0caa..c3230a21 100644
--- a/src/parser/mod.rs
+++ b/src/parser/mod.rs
@@ -1534,7 +1534,7 @@ impl<'a> Parser<'a> {
let loc = self.peek_token_ref().span.start;
let opt_expr = self.maybe_parse(|parser| {
match parser.parse_data_type()? {
- DataType::Interval => parser.parse_interval(),
+ DataType::Interval { .. } => parser.parse_interval(),
// PostgreSQL allows almost any identifier to be used as
custom data type name,
// and we support that in `parse_data_type()`. But unlike
Postgres we don't
// have a list of globally reserved keywords (since they vary
across dialects),
@@ -10066,10 +10066,18 @@ impl<'a> Parser<'a> {
self.parse_optional_precision()?,
TimezoneInfo::Tz,
)),
- // Interval types can be followed by a complicated interval
- // qualifier that we don't currently support. See
- // parse_interval for a taste.
- Keyword::INTERVAL => Ok(DataType::Interval),
+ Keyword::INTERVAL => {
+ if self.dialect.supports_interval_options() {
+ let fields =
self.maybe_parse_optional_interval_fields()?;
+ let precision = self.parse_optional_precision()?;
+ Ok(DataType::Interval { fields, precision })
+ } else {
+ Ok(DataType::Interval {
+ fields: None,
+ precision: None,
+ })
+ }
+ }
Keyword::JSON => Ok(DataType::JSON),
Keyword::JSONB => Ok(DataType::JSONB),
Keyword::REGCLASS => Ok(DataType::Regclass),
@@ -11038,6 +11046,85 @@ impl<'a> Parser<'a> {
}
}
+ fn maybe_parse_optional_interval_fields(
+ &mut self,
+ ) -> Result<Option<IntervalFields>, ParserError> {
+ match self.parse_one_of_keywords(&[
+ // Can be followed by `TO` option
+ Keyword::YEAR,
+ Keyword::DAY,
+ Keyword::HOUR,
+ Keyword::MINUTE,
+ // No `TO` option
+ Keyword::MONTH,
+ Keyword::SECOND,
+ ]) {
+ Some(Keyword::YEAR) => {
+ if self.peek_keyword(Keyword::TO) {
+ self.expect_keyword(Keyword::TO)?;
+ self.expect_keyword(Keyword::MONTH)?;
+ Ok(Some(IntervalFields::YearToMonth))
+ } else {
+ Ok(Some(IntervalFields::Year))
+ }
+ }
+ Some(Keyword::DAY) => {
+ if self.peek_keyword(Keyword::TO) {
+ self.expect_keyword(Keyword::TO)?;
+ match self.expect_one_of_keywords(&[
+ Keyword::HOUR,
+ Keyword::MINUTE,
+ Keyword::SECOND,
+ ])? {
+ Keyword::HOUR => Ok(Some(IntervalFields::DayToHour)),
+ Keyword::MINUTE =>
Ok(Some(IntervalFields::DayToMinute)),
+ Keyword::SECOND =>
Ok(Some(IntervalFields::DayToSecond)),
+ _ => {
+ self.prev_token();
+ self.expected("HOUR, MINUTE, or SECOND",
self.peek_token())
+ }
+ }
+ } else {
+ Ok(Some(IntervalFields::Day))
+ }
+ }
+ Some(Keyword::HOUR) => {
+ if self.peek_keyword(Keyword::TO) {
+ self.expect_keyword(Keyword::TO)?;
+ match self.expect_one_of_keywords(&[Keyword::MINUTE,
Keyword::SECOND])? {
+ Keyword::MINUTE =>
Ok(Some(IntervalFields::HourToMinute)),
+ Keyword::SECOND =>
Ok(Some(IntervalFields::HourToSecond)),
+ _ => {
+ self.prev_token();
+ self.expected("MINUTE or SECOND",
self.peek_token())
+ }
+ }
+ } else {
+ Ok(Some(IntervalFields::Hour))
+ }
+ }
+ Some(Keyword::MINUTE) => {
+ if self.peek_keyword(Keyword::TO) {
+ self.expect_keyword(Keyword::TO)?;
+ self.expect_keyword(Keyword::SECOND)?;
+ Ok(Some(IntervalFields::MinuteToSecond))
+ } else {
+ Ok(Some(IntervalFields::Minute))
+ }
+ }
+ Some(Keyword::MONTH) => Ok(Some(IntervalFields::Month)),
+ Some(Keyword::SECOND) => Ok(Some(IntervalFields::Second)),
+ Some(_) => {
+ self.prev_token();
+ self.expected(
+ "YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND",
+ self.peek_token(),
+ )
+ }
+ None => Ok(None),
+ }
+ }
+
/// Parse datetime64 [1]
/// Syntax
/// ```sql
diff --git a/tests/sqlparser_bigquery.rs b/tests/sqlparser_bigquery.rs
index 839ea6b8..5c14d140 100644
--- a/tests/sqlparser_bigquery.rs
+++ b/tests/sqlparser_bigquery.rs
@@ -961,7 +961,10 @@ fn parse_typed_struct_syntax_bigquery() {
})],
fields: vec![StructField {
field_name: None,
- field_type: DataType::Interval,
+ field_type: DataType::Interval {
+ fields: None,
+ precision: None
+ },
options: None,
}]
},
@@ -1300,7 +1303,10 @@ fn parse_typed_struct_syntax_bigquery_and_generic() {
})],
fields: vec![StructField {
field_name: None,
- field_type: DataType::Interval,
+ field_type: DataType::Interval {
+ fields: None,
+ precision: None
+ },
options: None,
}]
},
diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs
index 5e389aee..a64733d6 100644
--- a/tests/sqlparser_common.rs
+++ b/tests/sqlparser_common.rs
@@ -12955,7 +12955,10 @@ fn test_extract_seconds_ok() {
expr: Box::new(Expr::Value(
(Value::SingleQuotedString("2
seconds".to_string())).with_empty_span()
)),
- data_type: DataType::Interval,
+ data_type: DataType::Interval {
+ fields: None,
+ precision: None
+ },
format: None,
}),
}
@@ -12980,7 +12983,10 @@ fn test_extract_seconds_ok() {
expr: Box::new(Expr::Value(
(Value::SingleQuotedString("2
seconds".to_string())).with_empty_span(),
)),
- data_type: DataType::Interval,
+ data_type: DataType::Interval {
+ fields: None,
+ precision: None,
+ },
format: None,
}),
})],
@@ -13034,7 +13040,10 @@ fn test_extract_seconds_single_quote_ok() {
expr: Box::new(Expr::Value(
(Value::SingleQuotedString("2
seconds".to_string())).with_empty_span()
)),
- data_type: DataType::Interval,
+ data_type: DataType::Interval {
+ fields: None,
+ precision: None
+ },
format: None,
}),
}
diff --git a/tests/sqlparser_postgres.rs b/tests/sqlparser_postgres.rs
index 6ce4a483..6c00b1f1 100644
--- a/tests/sqlparser_postgres.rs
+++ b/tests/sqlparser_postgres.rs
@@ -5332,6 +5332,44 @@ fn parse_at_time_zone() {
);
}
+#[test]
+fn parse_interval_data_type() {
+ pg_and_generic().verified_stmt("CREATE TABLE t (i INTERVAL)");
+ for p in 0..=6 {
+ pg_and_generic().verified_stmt(&format!("CREATE TABLE t (i
INTERVAL({p}))"));
+ pg_and_generic().verified_stmt(&format!("SELECT '1
second'::INTERVAL({p})"));
+ pg_and_generic().verified_stmt(&format!("SELECT CAST('1 second' AS
INTERVAL({p}))"));
+ }
+ let fields = [
+ "YEAR",
+ "MONTH",
+ "DAY",
+ "HOUR",
+ "MINUTE",
+ "SECOND",
+ "YEAR TO MONTH",
+ "DAY TO HOUR",
+ "DAY TO MINUTE",
+ "DAY TO SECOND",
+ "HOUR TO MINUTE",
+ "HOUR TO SECOND",
+ "MINUTE TO SECOND",
+ ];
+ for field in fields {
+ pg_and_generic().verified_stmt(&format!("CREATE TABLE t (i INTERVAL
{field})"));
+ pg_and_generic().verified_stmt(&format!("SELECT '1 second'::INTERVAL
{field}"));
+ pg_and_generic().verified_stmt(&format!("SELECT CAST('1 second' AS
INTERVAL {field})"));
+ }
+ for p in 0..=6 {
+ for field in fields {
+ pg_and_generic().verified_stmt(&format!("CREATE TABLE t (i
INTERVAL {field}({p}))"));
+ pg_and_generic().verified_stmt(&format!("SELECT '1
second'::INTERVAL {field}({p})"));
+ pg_and_generic()
+ .verified_stmt(&format!("SELECT CAST('1 second' AS INTERVAL
{field}({p}))"));
+ }
+ }
+}
+
#[test]
fn parse_create_table_with_options() {
let sql = "CREATE TABLE t (c INT) WITH (foo = 'bar', a = 123)";
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@datafusion.apache.org
For additional commands, e-mail: commits-h...@datafusion.apache.org
