sqlglot.dialects.duckdb

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, generator, parser, tokens, transforms
  6from sqlglot.expressions import DATA_TYPE
  7from sqlglot.dialects.dialect import (
  8    Dialect,
  9    JSON_EXTRACT_TYPE,
 10    NormalizationStrategy,
 11    approx_count_distinct_sql,
 12    arg_max_or_min_no_count,
 13    arrow_json_extract_sql,
 14    binary_from_function,
 15    bool_xor_sql,
 16    build_default_decimal_type,
 17    date_trunc_to_time,
 18    datestrtodate_sql,
 19    no_datetime_sql,
 20    encode_decode_sql,
 21    build_formatted_time,
 22    inline_array_unless_query,
 23    no_comment_column_constraint_sql,
 24    no_safe_divide_sql,
 25    no_time_sql,
 26    no_timestamp_sql,
 27    pivot_column_names,
 28    regexp_extract_sql,
 29    rename_func,
 30    str_position_sql,
 31    str_to_time_sql,
 32    timestamptrunc_sql,
 33    timestrtotime_sql,
 34    unit_to_var,
 35)
 36from sqlglot.helper import seq_get
 37from sqlglot.tokens import TokenType
 38
 39DATETIME_DELTA = t.Union[
 40    exp.DateAdd, exp.TimeAdd, exp.DatetimeAdd, exp.TsOrDsAdd, exp.DateSub, exp.DatetimeSub
 41]
 42
 43
 44def _date_delta_sql(self: DuckDB.Generator, expression: DATETIME_DELTA) -> str:
 45    this = expression.this
 46    unit = unit_to_var(expression)
 47    op = (
 48        "+"
 49        if isinstance(expression, (exp.DateAdd, exp.TimeAdd, exp.DatetimeAdd, exp.TsOrDsAdd))
 50        else "-"
 51    )
 52
 53    to_type: t.Optional[DATA_TYPE] = None
 54    if isinstance(expression, exp.TsOrDsAdd):
 55        to_type = expression.return_type
 56    elif this.is_string:
 57        # Cast string literals (i.e function parameters) to the appropriate type for +/- interval to work
 58        to_type = (
 59            exp.DataType.Type.DATETIME
 60            if isinstance(expression, (exp.DatetimeAdd, exp.DatetimeSub))
 61            else exp.DataType.Type.DATE
 62        )
 63
 64    this = exp.cast(this, to_type) if to_type else this
 65
 66    return f"{self.sql(this)} {op} {self.sql(exp.Interval(this=expression.expression, unit=unit))}"
 67
 68
 69# BigQuery -> DuckDB conversion for the DATE function
 70def _date_sql(self: DuckDB.Generator, expression: exp.Date) -> str:
 71    result = f"CAST({self.sql(expression, 'this')} AS DATE)"
 72    zone = self.sql(expression, "zone")
 73
 74    if zone:
 75        date_str = self.func("STRFTIME", result, "'%d/%m/%Y'")
 76        date_str = f"{date_str} || ' ' || {zone}"
 77
 78        # This will create a TIMESTAMP with time zone information
 79        result = self.func("STRPTIME", date_str, "'%d/%m/%Y %Z'")
 80
 81    return result
 82
 83
 84def _array_sort_sql(self: DuckDB.Generator, expression: exp.ArraySort) -> str:
 85    if expression.expression:
 86        self.unsupported("DuckDB ARRAY_SORT does not support a comparator")
 87    return self.func("ARRAY_SORT", expression.this)
 88
 89
 90def _sort_array_sql(self: DuckDB.Generator, expression: exp.SortArray) -> str:
 91    name = "ARRAY_REVERSE_SORT" if expression.args.get("asc") == exp.false() else "ARRAY_SORT"
 92    return self.func(name, expression.this)
 93
 94
 95def _build_sort_array_desc(args: t.List) -> exp.Expression:
 96    return exp.SortArray(this=seq_get(args, 0), asc=exp.false())
 97
 98
 99def _build_date_diff(args: t.List) -> exp.Expression:
100    return exp.DateDiff(this=seq_get(args, 2), expression=seq_get(args, 1), unit=seq_get(args, 0))
101
102
103def _build_generate_series(end_exclusive: bool = False) -> t.Callable[[t.List], exp.GenerateSeries]:
104    def _builder(args: t.List) -> exp.GenerateSeries:
105        # Check https://duckdb.org/docs/sql/functions/nested.html#range-functions
106        if len(args) == 1:
107            # DuckDB uses 0 as a default for the series' start when it's omitted
108            args.insert(0, exp.Literal.number("0"))
109
110        gen_series = exp.GenerateSeries.from_arg_list(args)
111        gen_series.set("is_end_exclusive", end_exclusive)
112
113        return gen_series
114
115    return _builder
116
117
118def _build_make_timestamp(args: t.List) -> exp.Expression:
119    if len(args) == 1:
120        return exp.UnixToTime(this=seq_get(args, 0), scale=exp.UnixToTime.MICROS)
121
122    return exp.TimestampFromParts(
123        year=seq_get(args, 0),
124        month=seq_get(args, 1),
125        day=seq_get(args, 2),
126        hour=seq_get(args, 3),
127        min=seq_get(args, 4),
128        sec=seq_get(args, 5),
129    )
130
131
132def _struct_sql(self: DuckDB.Generator, expression: exp.Struct) -> str:
133    args: t.List[str] = []
134
135    # BigQuery allows inline construction such as "STRUCT<a STRING, b INTEGER>('str', 1)" which is
136    # canonicalized to "ROW('str', 1) AS STRUCT(a TEXT, b INT)" in DuckDB
137    # The transformation to ROW will take place if a cast to STRUCT / ARRAY of STRUCTs is found
138    ancestor_cast = expression.find_ancestor(exp.Cast)
139    is_struct_cast = ancestor_cast and any(
140        casted_type.is_type(exp.DataType.Type.STRUCT)
141        for casted_type in ancestor_cast.find_all(exp.DataType)
142    )
143
144    for i, expr in enumerate(expression.expressions):
145        is_property_eq = isinstance(expr, exp.PropertyEQ)
146        value = expr.expression if is_property_eq else expr
147
148        if is_struct_cast:
149            args.append(self.sql(value))
150        else:
151            key = expr.name if is_property_eq else f"_{i}"
152            args.append(f"{self.sql(exp.Literal.string(key))}: {self.sql(value)}")
153
154    csv_args = ", ".join(args)
155
156    return f"ROW({csv_args})" if is_struct_cast else f"{{{csv_args}}}"
157
158
159def _datatype_sql(self: DuckDB.Generator, expression: exp.DataType) -> str:
160    if expression.is_type("array"):
161        return f"{self.expressions(expression, flat=True)}[]"
162
163    # Type TIMESTAMP / TIME WITH TIME ZONE does not support any modifiers
164    if expression.is_type("timestamptz", "timetz"):
165        return expression.this.value
166
167    return self.datatype_sql(expression)
168
169
170def _json_format_sql(self: DuckDB.Generator, expression: exp.JSONFormat) -> str:
171    sql = self.func("TO_JSON", expression.this, expression.args.get("options"))
172    return f"CAST({sql} AS TEXT)"
173
174
175def _unix_to_time_sql(self: DuckDB.Generator, expression: exp.UnixToTime) -> str:
176    scale = expression.args.get("scale")
177    timestamp = expression.this
178
179    if scale in (None, exp.UnixToTime.SECONDS):
180        return self.func("TO_TIMESTAMP", timestamp)
181    if scale == exp.UnixToTime.MILLIS:
182        return self.func("EPOCH_MS", timestamp)
183    if scale == exp.UnixToTime.MICROS:
184        return self.func("MAKE_TIMESTAMP", timestamp)
185
186    return self.func("TO_TIMESTAMP", exp.Div(this=timestamp, expression=exp.func("POW", 10, scale)))
187
188
189def _arrow_json_extract_sql(self: DuckDB.Generator, expression: JSON_EXTRACT_TYPE) -> str:
190    arrow_sql = arrow_json_extract_sql(self, expression)
191    if not expression.same_parent and isinstance(expression.parent, (exp.Binary, exp.Bracket)):
192        arrow_sql = self.wrap(arrow_sql)
193    return arrow_sql
194
195
196class DuckDB(Dialect):
197    NULL_ORDERING = "nulls_are_last"
198    SUPPORTS_USER_DEFINED_TYPES = False
199    SAFE_DIVISION = True
200    INDEX_OFFSET = 1
201    CONCAT_COALESCE = True
202    SUPPORTS_ORDER_BY_ALL = True
203
204    # https://duckdb.org/docs/sql/introduction.html#creating-a-new-table
205    NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE
206
207    def to_json_path(self, path: t.Optional[exp.Expression]) -> t.Optional[exp.Expression]:
208        if isinstance(path, exp.Literal):
209            # DuckDB also supports the JSON pointer syntax, where every path starts with a `/`.
210            # Additionally, it allows accessing the back of lists using the `[#-i]` syntax.
211            # This check ensures we'll avoid trying to parse these as JSON paths, which can
212            # either result in a noisy warning or in an invalid representation of the path.
213            path_text = path.name
214            if path_text.startswith("/") or "[#" in path_text:
215                return path
216
217        return super().to_json_path(path)
218
219    class Tokenizer(tokens.Tokenizer):
220        HEREDOC_STRINGS = ["$"]
221
222        HEREDOC_TAG_IS_IDENTIFIER = True
223        HEREDOC_STRING_ALTERNATIVE = TokenType.PARAMETER
224
225        KEYWORDS = {
226            **tokens.Tokenizer.KEYWORDS,
227            "//": TokenType.DIV,
228            "ATTACH": TokenType.COMMAND,
229            "BINARY": TokenType.VARBINARY,
230            "BITSTRING": TokenType.BIT,
231            "BPCHAR": TokenType.TEXT,
232            "CHAR": TokenType.TEXT,
233            "CHARACTER VARYING": TokenType.TEXT,
234            "EXCLUDE": TokenType.EXCEPT,
235            "LOGICAL": TokenType.BOOLEAN,
236            "ONLY": TokenType.ONLY,
237            "PIVOT_WIDER": TokenType.PIVOT,
238            "POSITIONAL": TokenType.POSITIONAL,
239            "SIGNED": TokenType.INT,
240            "STRING": TokenType.TEXT,
241            "UBIGINT": TokenType.UBIGINT,
242            "UINTEGER": TokenType.UINT,
243            "USMALLINT": TokenType.USMALLINT,
244            "UTINYINT": TokenType.UTINYINT,
245            "TIMESTAMP_S": TokenType.TIMESTAMP_S,
246            "TIMESTAMP_MS": TokenType.TIMESTAMP_MS,
247            "TIMESTAMP_NS": TokenType.TIMESTAMP_NS,
248            "TIMESTAMP_US": TokenType.TIMESTAMP,
249            "VARCHAR": TokenType.TEXT,
250        }
251        KEYWORDS.pop("/*+")
252
253        SINGLE_TOKENS = {
254            **tokens.Tokenizer.SINGLE_TOKENS,
255            "$": TokenType.PARAMETER,
256        }
257
258    class Parser(parser.Parser):
259        BITWISE = {
260            **parser.Parser.BITWISE,
261            TokenType.TILDA: exp.RegexpLike,
262        }
263
264        FUNCTIONS_WITH_ALIASED_ARGS = {*parser.Parser.FUNCTIONS_WITH_ALIASED_ARGS, "STRUCT_PACK"}
265
266        FUNCTIONS = {
267            **parser.Parser.FUNCTIONS,
268            "ARRAY_REVERSE_SORT": _build_sort_array_desc,
269            "ARRAY_SORT": exp.SortArray.from_arg_list,
270            "DATEDIFF": _build_date_diff,
271            "DATE_DIFF": _build_date_diff,
272            "DATE_TRUNC": date_trunc_to_time,
273            "DATETRUNC": date_trunc_to_time,
274            "DECODE": lambda args: exp.Decode(
275                this=seq_get(args, 0), charset=exp.Literal.string("utf-8")
276            ),
277            "ENCODE": lambda args: exp.Encode(
278                this=seq_get(args, 0), charset=exp.Literal.string("utf-8")
279            ),
280            "EPOCH": exp.TimeToUnix.from_arg_list,
281            "EPOCH_MS": lambda args: exp.UnixToTime(
282                this=seq_get(args, 0), scale=exp.UnixToTime.MILLIS
283            ),
284            "JSON": exp.ParseJSON.from_arg_list,
285            "JSON_EXTRACT_PATH": parser.build_extract_json_with_path(exp.JSONExtract),
286            "JSON_EXTRACT_STRING": parser.build_extract_json_with_path(exp.JSONExtractScalar),
287            "LIST_HAS": exp.ArrayContains.from_arg_list,
288            "LIST_REVERSE_SORT": _build_sort_array_desc,
289            "LIST_SORT": exp.SortArray.from_arg_list,
290            "LIST_VALUE": exp.Array.from_arg_list,
291            "MAKE_TIME": exp.TimeFromParts.from_arg_list,
292            "MAKE_TIMESTAMP": _build_make_timestamp,
293            "MEDIAN": lambda args: exp.PercentileCont(
294                this=seq_get(args, 0), expression=exp.Literal.number(0.5)
295            ),
296            "QUANTILE_CONT": exp.PercentileCont.from_arg_list,
297            "QUANTILE_DISC": exp.PercentileDisc.from_arg_list,
298            "REGEXP_EXTRACT": lambda args: exp.RegexpExtract(
299                this=seq_get(args, 0), expression=seq_get(args, 1), group=seq_get(args, 2)
300            ),
301            "REGEXP_MATCHES": exp.RegexpLike.from_arg_list,
302            "REGEXP_REPLACE": lambda args: exp.RegexpReplace(
303                this=seq_get(args, 0),
304                expression=seq_get(args, 1),
305                replacement=seq_get(args, 2),
306                modifiers=seq_get(args, 3),
307            ),
308            "STRFTIME": build_formatted_time(exp.TimeToStr, "duckdb"),
309            "STRING_SPLIT": exp.Split.from_arg_list,
310            "STRING_SPLIT_REGEX": exp.RegexpSplit.from_arg_list,
311            "STRING_TO_ARRAY": exp.Split.from_arg_list,
312            "STRPTIME": build_formatted_time(exp.StrToTime, "duckdb"),
313            "STRUCT_PACK": exp.Struct.from_arg_list,
314            "STR_SPLIT": exp.Split.from_arg_list,
315            "STR_SPLIT_REGEX": exp.RegexpSplit.from_arg_list,
316            "TO_TIMESTAMP": exp.UnixToTime.from_arg_list,
317            "UNNEST": exp.Explode.from_arg_list,
318            "XOR": binary_from_function(exp.BitwiseXor),
319            "GENERATE_SERIES": _build_generate_series(),
320            "RANGE": _build_generate_series(end_exclusive=True),
321        }
322
323        FUNCTIONS.pop("DATE_SUB")
324
325        FUNCTION_PARSERS = parser.Parser.FUNCTION_PARSERS.copy()
326        FUNCTION_PARSERS.pop("DECODE")
327
328        NO_PAREN_FUNCTION_PARSERS = {
329            **parser.Parser.NO_PAREN_FUNCTION_PARSERS,
330            "MAP": lambda self: self._parse_map(),
331        }
332
333        TABLE_ALIAS_TOKENS = parser.Parser.TABLE_ALIAS_TOKENS - {
334            TokenType.SEMI,
335            TokenType.ANTI,
336        }
337
338        PLACEHOLDER_PARSERS = {
339            **parser.Parser.PLACEHOLDER_PARSERS,
340            TokenType.PARAMETER: lambda self: (
341                self.expression(exp.Placeholder, this=self._prev.text)
342                if self._match(TokenType.NUMBER) or self._match_set(self.ID_VAR_TOKENS)
343                else None
344            ),
345        }
346
347        TYPE_CONVERTERS = {
348            # https://duckdb.org/docs/sql/data_types/numeric
349            exp.DataType.Type.DECIMAL: build_default_decimal_type(precision=18, scale=3),
350            # https://duckdb.org/docs/sql/data_types/text
351            exp.DataType.Type.TEXT: lambda dtype: exp.DataType.build("TEXT"),
352        }
353
354        def _parse_table_sample(self, as_modifier: bool = False) -> t.Optional[exp.TableSample]:
355            # https://duckdb.org/docs/sql/samples.html
356            sample = super()._parse_table_sample(as_modifier=as_modifier)
357            if sample and not sample.args.get("method"):
358                if sample.args.get("size"):
359                    sample.set("method", exp.var("RESERVOIR"))
360                else:
361                    sample.set("method", exp.var("SYSTEM"))
362
363            return sample
364
365        def _parse_bracket(
366            self, this: t.Optional[exp.Expression] = None
367        ) -> t.Optional[exp.Expression]:
368            bracket = super()._parse_bracket(this)
369            if isinstance(bracket, exp.Bracket):
370                bracket.set("returns_list_for_maps", True)
371
372            return bracket
373
374        def _parse_map(self) -> exp.ToMap | exp.Map:
375            if self._match(TokenType.L_BRACE, advance=False):
376                return self.expression(exp.ToMap, this=self._parse_bracket())
377
378            args = self._parse_wrapped_csv(self._parse_assignment)
379            return self.expression(exp.Map, keys=seq_get(args, 0), values=seq_get(args, 1))
380
381        def _parse_struct_types(self, type_required: bool = False) -> t.Optional[exp.Expression]:
382            return self._parse_field_def()
383
384        def _pivot_column_names(self, aggregations: t.List[exp.Expression]) -> t.List[str]:
385            if len(aggregations) == 1:
386                return super()._pivot_column_names(aggregations)
387            return pivot_column_names(aggregations, dialect="duckdb")
388
389    class Generator(generator.Generator):
390        PARAMETER_TOKEN = "$"
391        NAMED_PLACEHOLDER_TOKEN = "$"
392        JOIN_HINTS = False
393        TABLE_HINTS = False
394        QUERY_HINTS = False
395        LIMIT_FETCH = "LIMIT"
396        STRUCT_DELIMITER = ("(", ")")
397        RENAME_TABLE_WITH_DB = False
398        NVL2_SUPPORTED = False
399        SEMI_ANTI_JOIN_WITH_SIDE = False
400        TABLESAMPLE_KEYWORDS = "USING SAMPLE"
401        TABLESAMPLE_SEED_KEYWORD = "REPEATABLE"
402        LAST_DAY_SUPPORTS_DATE_PART = False
403        JSON_KEY_VALUE_PAIR_SEP = ","
404        IGNORE_NULLS_IN_FUNC = True
405        JSON_PATH_BRACKETED_KEY_SUPPORTED = False
406        SUPPORTS_CREATE_TABLE_LIKE = False
407        MULTI_ARG_DISTINCT = False
408        CAN_IMPLEMENT_ARRAY_ANY = True
409        SUPPORTS_TO_NUMBER = False
410        COPY_HAS_INTO_KEYWORD = False
411        STAR_EXCEPT = "EXCLUDE"
412        PAD_FILL_PATTERN_IS_REQUIRED = True
413
414        TRANSFORMS = {
415            **generator.Generator.TRANSFORMS,
416            exp.ApproxDistinct: approx_count_distinct_sql,
417            exp.Array: inline_array_unless_query,
418            exp.ArrayContainsAll: rename_func("ARRAY_HAS_ALL"),
419            exp.ArrayFilter: rename_func("LIST_FILTER"),
420            exp.ArraySize: rename_func("ARRAY_LENGTH"),
421            exp.ArgMax: arg_max_or_min_no_count("ARG_MAX"),
422            exp.ArgMin: arg_max_or_min_no_count("ARG_MIN"),
423            exp.ArraySort: _array_sort_sql,
424            exp.ArraySum: rename_func("LIST_SUM"),
425            exp.BitwiseXor: rename_func("XOR"),
426            exp.CommentColumnConstraint: no_comment_column_constraint_sql,
427            exp.CurrentDate: lambda *_: "CURRENT_DATE",
428            exp.CurrentTime: lambda *_: "CURRENT_TIME",
429            exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
430            exp.DayOfMonth: rename_func("DAYOFMONTH"),
431            exp.DayOfWeek: rename_func("DAYOFWEEK"),
432            exp.DayOfYear: rename_func("DAYOFYEAR"),
433            exp.DataType: _datatype_sql,
434            exp.Date: _date_sql,
435            exp.DateAdd: _date_delta_sql,
436            exp.DateFromParts: rename_func("MAKE_DATE"),
437            exp.DateSub: _date_delta_sql,
438            exp.DateDiff: lambda self, e: self.func(
439                "DATE_DIFF", f"'{e.args.get('unit') or 'DAY'}'", e.expression, e.this
440            ),
441            exp.DateStrToDate: datestrtodate_sql,
442            exp.Datetime: no_datetime_sql,
443            exp.DatetimeSub: _date_delta_sql,
444            exp.DatetimeAdd: _date_delta_sql,
445            exp.DateToDi: lambda self,
446            e: f"CAST(STRFTIME({self.sql(e, 'this')}, {DuckDB.DATEINT_FORMAT}) AS INT)",
447            exp.Decode: lambda self, e: encode_decode_sql(self, e, "DECODE", replace=False),
448            exp.DiToDate: lambda self,
449            e: f"CAST(STRPTIME(CAST({self.sql(e, 'this')} AS TEXT), {DuckDB.DATEINT_FORMAT}) AS DATE)",
450            exp.Encode: lambda self, e: encode_decode_sql(self, e, "ENCODE", replace=False),
451            exp.Explode: rename_func("UNNEST"),
452            exp.IntDiv: lambda self, e: self.binary(e, "//"),
453            exp.IsInf: rename_func("ISINF"),
454            exp.IsNan: rename_func("ISNAN"),
455            exp.JSONExtract: _arrow_json_extract_sql,
456            exp.JSONExtractScalar: _arrow_json_extract_sql,
457            exp.JSONFormat: _json_format_sql,
458            exp.LogicalOr: rename_func("BOOL_OR"),
459            exp.LogicalAnd: rename_func("BOOL_AND"),
460            exp.MD5Digest: lambda self, e: self.func("UNHEX", self.func("MD5", e.this)),
461            exp.MonthsBetween: lambda self, e: self.func(
462                "DATEDIFF",
463                "'month'",
464                exp.cast(e.expression, exp.DataType.Type.TIMESTAMP, copy=True),
465                exp.cast(e.this, exp.DataType.Type.TIMESTAMP, copy=True),
466            ),
467            exp.PercentileCont: rename_func("QUANTILE_CONT"),
468            exp.PercentileDisc: rename_func("QUANTILE_DISC"),
469            # DuckDB doesn't allow qualified columns inside of PIVOT expressions.
470            # See: https://github.com/duckdb/duckdb/blob/671faf92411182f81dce42ac43de8bfb05d9909e/src/planner/binder/tableref/bind_pivot.cpp#L61-L62
471            exp.Pivot: transforms.preprocess([transforms.unqualify_columns]),
472            exp.RegexpExtract: regexp_extract_sql,
473            exp.RegexpReplace: lambda self, e: self.func(
474                "REGEXP_REPLACE",
475                e.this,
476                e.expression,
477                e.args.get("replacement"),
478                e.args.get("modifiers"),
479            ),
480            exp.RegexpLike: rename_func("REGEXP_MATCHES"),
481            exp.RegexpSplit: rename_func("STR_SPLIT_REGEX"),
482            exp.Return: lambda self, e: self.sql(e, "this"),
483            exp.ReturnsProperty: lambda self, e: "TABLE" if isinstance(e.this, exp.Schema) else "",
484            exp.Rand: rename_func("RANDOM"),
485            exp.SafeDivide: no_safe_divide_sql,
486            exp.Split: rename_func("STR_SPLIT"),
487            exp.SortArray: _sort_array_sql,
488            exp.StrPosition: str_position_sql,
489            exp.StrToUnix: lambda self, e: self.func(
490                "EPOCH", self.func("STRPTIME", e.this, self.format_time(e))
491            ),
492            exp.Struct: _struct_sql,
493            exp.TimeAdd: _date_delta_sql,
494            exp.Time: no_time_sql,
495            exp.Timestamp: no_timestamp_sql,
496            exp.TimestampDiff: lambda self, e: self.func(
497                "DATE_DIFF", exp.Literal.string(e.unit), e.expression, e.this
498            ),
499            exp.TimestampTrunc: timestamptrunc_sql(),
500            exp.TimeStrToDate: lambda self, e: self.sql(exp.cast(e.this, exp.DataType.Type.DATE)),
501            exp.TimeStrToTime: timestrtotime_sql,
502            exp.TimeStrToUnix: lambda self, e: self.func(
503                "EPOCH", exp.cast(e.this, exp.DataType.Type.TIMESTAMP)
504            ),
505            exp.TimeToStr: lambda self, e: self.func("STRFTIME", e.this, self.format_time(e)),
506            exp.TimeToUnix: rename_func("EPOCH"),
507            exp.TsOrDiToDi: lambda self,
508            e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS TEXT), '-', ''), 1, 8) AS INT)",
509            exp.TsOrDsAdd: _date_delta_sql,
510            exp.TsOrDsDiff: lambda self, e: self.func(
511                "DATE_DIFF",
512                f"'{e.args.get('unit') or 'DAY'}'",
513                exp.cast(e.expression, exp.DataType.Type.TIMESTAMP),
514                exp.cast(e.this, exp.DataType.Type.TIMESTAMP),
515            ),
516            exp.UnixToStr: lambda self, e: self.func(
517                "STRFTIME", self.func("TO_TIMESTAMP", e.this), self.format_time(e)
518            ),
519            exp.UnixToTime: _unix_to_time_sql,
520            exp.UnixToTimeStr: lambda self, e: f"CAST(TO_TIMESTAMP({self.sql(e, 'this')}) AS TEXT)",
521            exp.VariancePop: rename_func("VAR_POP"),
522            exp.WeekOfYear: rename_func("WEEKOFYEAR"),
523            exp.Xor: bool_xor_sql,
524        }
525
526        SUPPORTED_JSON_PATH_PARTS = {
527            exp.JSONPathKey,
528            exp.JSONPathRoot,
529            exp.JSONPathSubscript,
530            exp.JSONPathWildcard,
531        }
532
533        TYPE_MAPPING = {
534            **generator.Generator.TYPE_MAPPING,
535            exp.DataType.Type.BINARY: "BLOB",
536            exp.DataType.Type.BPCHAR: "TEXT",
537            exp.DataType.Type.CHAR: "TEXT",
538            exp.DataType.Type.FLOAT: "REAL",
539            exp.DataType.Type.NCHAR: "TEXT",
540            exp.DataType.Type.NVARCHAR: "TEXT",
541            exp.DataType.Type.UINT: "UINTEGER",
542            exp.DataType.Type.VARBINARY: "BLOB",
543            exp.DataType.Type.ROWVERSION: "BLOB",
544            exp.DataType.Type.VARCHAR: "TEXT",
545            exp.DataType.Type.TIMESTAMPNTZ: "TIMESTAMP",
546            exp.DataType.Type.TIMESTAMP_S: "TIMESTAMP_S",
547            exp.DataType.Type.TIMESTAMP_MS: "TIMESTAMP_MS",
548            exp.DataType.Type.TIMESTAMP_NS: "TIMESTAMP_NS",
549        }
550
551        # https://github.com/duckdb/duckdb/blob/ff7f24fd8e3128d94371827523dae85ebaf58713/third_party/libpg_query/grammar/keywords/reserved_keywords.list#L1-L77
552        RESERVED_KEYWORDS = {
553            "array",
554            "analyse",
555            "union",
556            "all",
557            "when",
558            "in_p",
559            "default",
560            "create_p",
561            "window",
562            "asymmetric",
563            "to",
564            "else",
565            "localtime",
566            "from",
567            "end_p",
568            "select",
569            "current_date",
570            "foreign",
571            "with",
572            "grant",
573            "session_user",
574            "or",
575            "except",
576            "references",
577            "fetch",
578            "limit",
579            "group_p",
580            "leading",
581            "into",
582            "collate",
583            "offset",
584            "do",
585            "then",
586            "localtimestamp",
587            "check_p",
588            "lateral_p",
589            "current_role",
590            "where",
591            "asc_p",
592            "placing",
593            "desc_p",
594            "user",
595            "unique",
596            "initially",
597            "column",
598            "both",
599            "some",
600            "as",
601            "any",
602            "only",
603            "deferrable",
604            "null_p",
605            "current_time",
606            "true_p",
607            "table",
608            "case",
609            "trailing",
610            "variadic",
611            "for",
612            "on",
613            "distinct",
614            "false_p",
615            "not",
616            "constraint",
617            "current_timestamp",
618            "returning",
619            "primary",
620            "intersect",
621            "having",
622            "analyze",
623            "current_user",
624            "and",
625            "cast",
626            "symmetric",
627            "using",
628            "order",
629            "current_catalog",
630        }
631
632        UNWRAPPED_INTERVAL_VALUES = (exp.Literal, exp.Paren)
633
634        # DuckDB doesn't generally support CREATE TABLE .. properties
635        # https://duckdb.org/docs/sql/statements/create_table.html
636        PROPERTIES_LOCATION = {
637            prop: exp.Properties.Location.UNSUPPORTED
638            for prop in generator.Generator.PROPERTIES_LOCATION
639        }
640
641        # There are a few exceptions (e.g. temporary tables) which are supported or
642        # can be transpiled to DuckDB, so we explicitly override them accordingly
643        PROPERTIES_LOCATION[exp.LikeProperty] = exp.Properties.Location.POST_SCHEMA
644        PROPERTIES_LOCATION[exp.TemporaryProperty] = exp.Properties.Location.POST_CREATE
645        PROPERTIES_LOCATION[exp.ReturnsProperty] = exp.Properties.Location.POST_ALIAS
646
647        def strtotime_sql(self, expression: exp.StrToTime) -> str:
648            if expression.args.get("safe"):
649                formatted_time = self.format_time(expression)
650                return f"CAST({self.func('TRY_STRPTIME', expression.this, formatted_time)} AS TIMESTAMP)"
651            return str_to_time_sql(self, expression)
652
653        def strtodate_sql(self, expression: exp.StrToDate) -> str:
654            if expression.args.get("safe"):
655                formatted_time = self.format_time(expression)
656                return f"CAST({self.func('TRY_STRPTIME', expression.this, formatted_time)} AS DATE)"
657            return f"CAST({str_to_time_sql(self, expression)} AS DATE)"
658
659        def parsejson_sql(self, expression: exp.ParseJSON) -> str:
660            arg = expression.this
661            if expression.args.get("safe"):
662                return self.sql(exp.case().when(exp.func("json_valid", arg), arg).else_(exp.null()))
663            return self.func("JSON", arg)
664
665        def timefromparts_sql(self, expression: exp.TimeFromParts) -> str:
666            nano = expression.args.get("nano")
667            if nano is not None:
668                expression.set(
669                    "sec", expression.args["sec"] + nano.pop() / exp.Literal.number(1000000000.0)
670                )
671
672            return rename_func("MAKE_TIME")(self, expression)
673
674        def timestampfromparts_sql(self, expression: exp.TimestampFromParts) -> str:
675            sec = expression.args["sec"]
676
677            milli = expression.args.get("milli")
678            if milli is not None:
679                sec += milli.pop() / exp.Literal.number(1000.0)
680
681            nano = expression.args.get("nano")
682            if nano is not None:
683                sec += nano.pop() / exp.Literal.number(1000000000.0)
684
685            if milli or nano:
686                expression.set("sec", sec)
687
688            return rename_func("MAKE_TIMESTAMP")(self, expression)
689
690        def tablesample_sql(
691            self,
692            expression: exp.TableSample,
693            sep: str = " AS ",
694            tablesample_keyword: t.Optional[str] = None,
695        ) -> str:
696            if not isinstance(expression.parent, exp.Select):
697                # This sample clause only applies to a single source, not the entire resulting relation
698                tablesample_keyword = "TABLESAMPLE"
699
700            if expression.args.get("size"):
701                method = expression.args.get("method")
702                if method and method.name.upper() != "RESERVOIR":
703                    self.unsupported(
704                        f"Sampling method {method} is not supported with a discrete sample count, "
705                        "defaulting to reservoir sampling"
706                    )
707                    expression.set("method", exp.var("RESERVOIR"))
708
709            return super().tablesample_sql(
710                expression, sep=sep, tablesample_keyword=tablesample_keyword
711            )
712
713        def interval_sql(self, expression: exp.Interval) -> str:
714            multiplier: t.Optional[int] = None
715            unit = expression.text("unit").lower()
716
717            if unit.startswith("week"):
718                multiplier = 7
719            if unit.startswith("quarter"):
720                multiplier = 90
721
722            if multiplier:
723                return f"({multiplier} * {super().interval_sql(exp.Interval(this=expression.this, unit=exp.var('DAY')))})"
724
725            return super().interval_sql(expression)
726
727        def columndef_sql(self, expression: exp.ColumnDef, sep: str = " ") -> str:
728            if isinstance(expression.parent, exp.UserDefinedFunction):
729                return self.sql(expression, "this")
730            return super().columndef_sql(expression, sep)
731
732        def join_sql(self, expression: exp.Join) -> str:
733            if (
734                expression.side == "LEFT"
735                and not expression.args.get("on")
736                and isinstance(expression.this, exp.Unnest)
737            ):
738                # Some dialects support `LEFT JOIN UNNEST(...)` without an explicit ON clause
739                # DuckDB doesn't, but we can just add a dummy ON clause that is always true
740                return super().join_sql(expression.on(exp.true()))
741
742            return super().join_sql(expression)
743
744        def generateseries_sql(self, expression: exp.GenerateSeries) -> str:
745            # GENERATE_SERIES(a, b) -> [a, b], RANGE(a, b) -> [a, b)
746            if expression.args.get("is_end_exclusive"):
747                expression.set("is_end_exclusive", None)
748                return rename_func("RANGE")(self, expression)
749
750            return super().generateseries_sql(expression)
751
752        def bracket_sql(self, expression: exp.Bracket) -> str:
753            this = expression.this
754            if isinstance(this, exp.Array):
755                this.replace(exp.paren(this))
756
757            bracket = super().bracket_sql(expression)
758
759            if not expression.args.get("returns_list_for_maps"):
760                if not this.type:
761                    from sqlglot.optimizer.annotate_types import annotate_types
762
763                    this = annotate_types(this)
764
765                if this.is_type(exp.DataType.Type.MAP):
766                    bracket = f"({bracket})[1]"
767
768            return bracket
769
770        def withingroup_sql(self, expression: exp.WithinGroup) -> str:
771            expression_sql = self.sql(expression, "expression")
772
773            func = expression.this
774            if isinstance(func, exp.PERCENTILES):
775                # Make the order key the first arg and slide the fraction to the right
776                # https://duckdb.org/docs/sql/aggregates#ordered-set-aggregate-functions
777                order_col = expression.find(exp.Ordered)
778                if order_col:
779                    func.set("expression", func.this)
780                    func.set("this", order_col.this)
781
782            this = self.sql(expression, "this").rstrip(")")
783
784            return f"{this}{expression_sql})"
785
786        def length_sql(self, expression: exp.Length) -> str:
787            arg = expression.this
788
789            # Dialects like BQ and Snowflake also accept binary values as args, so
790            # DDB will attempt to infer the type or resort to case/when resolution
791            if not expression.args.get("binary") or arg.is_string:
792                return self.func("LENGTH", arg)
793
794            if not arg.type:
795                from sqlglot.optimizer.annotate_types import annotate_types
796
797                arg = annotate_types(arg)
798
799            if arg.is_type(*exp.DataType.TEXT_TYPES):
800                return self.func("LENGTH", arg)
801
802            # We need these casts to make duckdb's static type checker happy
803            blob = exp.cast(arg, exp.DataType.Type.VARBINARY)
804            varchar = exp.cast(arg, exp.DataType.Type.VARCHAR)
805
806            case = (
807                exp.case(self.func("TYPEOF", arg))
808                .when(
809                    "'VARCHAR'", exp.Anonymous(this="LENGTH", expressions=[varchar])
810                )  # anonymous to break length_sql recursion
811                .when("'BLOB'", self.func("OCTET_LENGTH", blob))
812            )
813
814            return self.sql(case)
815
816        def objectinsert_sql(self, expression: exp.ObjectInsert) -> str:
817            this = expression.this
818            key = expression.args.get("key")
819            key_sql = key.name if isinstance(key, exp.Expression) else ""
820            value_sql = self.sql(expression, "value")
821
822            kv_sql = f"{key_sql} := {value_sql}"
823
824            # If the input struct is empty e.g. transpiling OBJECT_INSERT(OBJECT_CONSTRUCT(), key, value) from Snowflake
825            # then we can generate STRUCT_PACK which will build it since STRUCT_INSERT({}, key := value) is not valid DuckDB
826            if isinstance(this, exp.Struct) and not this.expressions:
827                return self.func("STRUCT_PACK", kv_sql)
828
829            return self.func("STRUCT_INSERT", this, kv_sql)