sqlglot.dialects.postgres

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, generator, parser, tokens, transforms
  6from sqlglot.dialects.dialect import (
  7    DATE_ADD_OR_SUB,
  8    Dialect,
  9    JSON_EXTRACT_TYPE,
 10    any_value_to_max_sql,
 11    bool_xor_sql,
 12    datestrtodate_sql,
 13    build_formatted_time,
 14    filter_array_using_unnest,
 15    json_extract_segments,
 16    json_path_key_only_name,
 17    max_or_greatest,
 18    merge_without_target_sql,
 19    min_or_least,
 20    no_last_day_sql,
 21    no_map_from_entries_sql,
 22    no_paren_current_date_sql,
 23    no_pivot_sql,
 24    no_trycast_sql,
 25    build_json_extract_path,
 26    build_timestamp_trunc,
 27    rename_func,
 28    str_position_sql,
 29    struct_extract_sql,
 30    timestamptrunc_sql,
 31    timestrtotime_sql,
 32    trim_sql,
 33    ts_or_ds_add_cast,
 34)
 35from sqlglot.helper import is_int, seq_get
 36from sqlglot.parser import binary_range_parser
 37from sqlglot.tokens import TokenType
 38
 39DATE_DIFF_FACTOR = {
 40    "MICROSECOND": " * 1000000",
 41    "MILLISECOND": " * 1000",
 42    "SECOND": "",
 43    "MINUTE": " / 60",
 44    "HOUR": " / 3600",
 45    "DAY": " / 86400",
 46}
 47
 48
 49def _date_add_sql(kind: str) -> t.Callable[[Postgres.Generator, DATE_ADD_OR_SUB], str]:
 50    def func(self: Postgres.Generator, expression: DATE_ADD_OR_SUB) -> str:
 51        if isinstance(expression, exp.TsOrDsAdd):
 52            expression = ts_or_ds_add_cast(expression)
 53
 54        this = self.sql(expression, "this")
 55        unit = expression.args.get("unit")
 56
 57        expression = self._simplify_unless_literal(expression.expression)
 58        if not isinstance(expression, exp.Literal):
 59            self.unsupported("Cannot add non literal")
 60
 61        expression.args["is_string"] = True
 62        return f"{this} {kind} {self.sql(exp.Interval(this=expression, unit=unit))}"
 63
 64    return func
 65
 66
 67def _date_diff_sql(self: Postgres.Generator, expression: exp.DateDiff) -> str:
 68    unit = expression.text("unit").upper()
 69    factor = DATE_DIFF_FACTOR.get(unit)
 70
 71    end = f"CAST({self.sql(expression, 'this')} AS TIMESTAMP)"
 72    start = f"CAST({self.sql(expression, 'expression')} AS TIMESTAMP)"
 73
 74    if factor is not None:
 75        return f"CAST(EXTRACT(epoch FROM {end} - {start}){factor} AS BIGINT)"
 76
 77    age = f"AGE({end}, {start})"
 78
 79    if unit == "WEEK":
 80        unit = f"EXTRACT(days FROM ({end} - {start})) / 7"
 81    elif unit == "MONTH":
 82        unit = f"EXTRACT(year FROM {age}) * 12 + EXTRACT(month FROM {age})"
 83    elif unit == "QUARTER":
 84        unit = f"EXTRACT(year FROM {age}) * 4 + EXTRACT(month FROM {age}) / 3"
 85    elif unit == "YEAR":
 86        unit = f"EXTRACT(year FROM {age})"
 87    else:
 88        unit = age
 89
 90    return f"CAST({unit} AS BIGINT)"
 91
 92
 93def _substring_sql(self: Postgres.Generator, expression: exp.Substring) -> str:
 94    this = self.sql(expression, "this")
 95    start = self.sql(expression, "start")
 96    length = self.sql(expression, "length")
 97
 98    from_part = f" FROM {start}" if start else ""
 99    for_part = f" FOR {length}" if length else ""
100
101    return f"SUBSTRING({this}{from_part}{for_part})"
102
103
104def _string_agg_sql(self: Postgres.Generator, expression: exp.GroupConcat) -> str:
105    separator = expression.args.get("separator") or exp.Literal.string(",")
106
107    order = ""
108    this = expression.this
109    if isinstance(this, exp.Order):
110        if this.this:
111            this = this.this.pop()
112        order = self.sql(expression.this)  # Order has a leading space
113
114    return f"STRING_AGG({self.format_args(this, separator)}{order})"
115
116
117def _auto_increment_to_serial(expression: exp.Expression) -> exp.Expression:
118    auto = expression.find(exp.AutoIncrementColumnConstraint)
119
120    if auto:
121        expression.args["constraints"].remove(auto.parent)
122        kind = expression.args["kind"]
123
124        if kind.this == exp.DataType.Type.INT:
125            kind.replace(exp.DataType(this=exp.DataType.Type.SERIAL))
126        elif kind.this == exp.DataType.Type.SMALLINT:
127            kind.replace(exp.DataType(this=exp.DataType.Type.SMALLSERIAL))
128        elif kind.this == exp.DataType.Type.BIGINT:
129            kind.replace(exp.DataType(this=exp.DataType.Type.BIGSERIAL))
130
131    return expression
132
133
134def _serial_to_generated(expression: exp.Expression) -> exp.Expression:
135    if not isinstance(expression, exp.ColumnDef):
136        return expression
137    kind = expression.kind
138    if not kind:
139        return expression
140
141    if kind.this == exp.DataType.Type.SERIAL:
142        data_type = exp.DataType(this=exp.DataType.Type.INT)
143    elif kind.this == exp.DataType.Type.SMALLSERIAL:
144        data_type = exp.DataType(this=exp.DataType.Type.SMALLINT)
145    elif kind.this == exp.DataType.Type.BIGSERIAL:
146        data_type = exp.DataType(this=exp.DataType.Type.BIGINT)
147    else:
148        data_type = None
149
150    if data_type:
151        expression.args["kind"].replace(data_type)
152        constraints = expression.args["constraints"]
153        generated = exp.ColumnConstraint(kind=exp.GeneratedAsIdentityColumnConstraint(this=False))
154        notnull = exp.ColumnConstraint(kind=exp.NotNullColumnConstraint())
155
156        if notnull not in constraints:
157            constraints.insert(0, notnull)
158        if generated not in constraints:
159            constraints.insert(0, generated)
160
161    return expression
162
163
164def _build_generate_series(args: t.List) -> exp.GenerateSeries:
165    # The goal is to convert step values like '1 day' or INTERVAL '1 day' into INTERVAL '1' day
166    step = seq_get(args, 2)
167
168    if step is None:
169        # Postgres allows calls with just two arguments -- the "step" argument defaults to 1
170        return exp.GenerateSeries.from_arg_list(args)
171
172    if step.is_string:
173        args[2] = exp.to_interval(step.this)
174    elif isinstance(step, exp.Interval) and not step.args.get("unit"):
175        args[2] = exp.to_interval(step.this.this)
176
177    return exp.GenerateSeries.from_arg_list(args)
178
179
180def _build_to_timestamp(args: t.List) -> exp.UnixToTime | exp.StrToTime:
181    # TO_TIMESTAMP accepts either a single double argument or (text, text)
182    if len(args) == 1:
183        # https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TABLE
184        return exp.UnixToTime.from_arg_list(args)
185
186    # https://www.postgresql.org/docs/current/functions-formatting.html
187    return build_formatted_time(exp.StrToTime, "postgres")(args)
188
189
190def _json_extract_sql(
191    name: str, op: str
192) -> t.Callable[[Postgres.Generator, JSON_EXTRACT_TYPE], str]:
193    def _generate(self: Postgres.Generator, expression: JSON_EXTRACT_TYPE) -> str:
194        if expression.args.get("only_json_types"):
195            return json_extract_segments(name, quoted_index=False, op=op)(self, expression)
196        return json_extract_segments(name)(self, expression)
197
198    return _generate
199
200
201def _build_regexp_replace(args: t.List) -> exp.RegexpReplace:
202    # The signature of REGEXP_REPLACE is:
203    # regexp_replace(source, pattern, replacement [, start [, N ]] [, flags ])
204    #
205    # Any one of `start`, `N` and `flags` can be column references, meaning that
206    # unless we can statically see that the last argument is a non-integer string
207    # (eg. not '0'), then it's not possible to construct the correct AST
208    if len(args) > 3:
209        last = args[-1]
210        if not is_int(last.name):
211            if not last.type or last.is_type(exp.DataType.Type.UNKNOWN, exp.DataType.Type.NULL):
212                from sqlglot.optimizer.annotate_types import annotate_types
213
214                last = annotate_types(last)
215
216            if last.is_type(*exp.DataType.TEXT_TYPES):
217                regexp_replace = exp.RegexpReplace.from_arg_list(args[:-1])
218                regexp_replace.set("modifiers", last)
219                return regexp_replace
220
221    return exp.RegexpReplace.from_arg_list(args)
222
223
224def _unix_to_time_sql(self: Postgres.Generator, expression: exp.UnixToTime) -> str:
225    scale = expression.args.get("scale")
226    timestamp = expression.this
227
228    if scale in (None, exp.UnixToTime.SECONDS):
229        return self.func("TO_TIMESTAMP", timestamp, self.format_time(expression))
230
231    return self.func(
232        "TO_TIMESTAMP",
233        exp.Div(this=timestamp, expression=exp.func("POW", 10, scale)),
234        self.format_time(expression),
235    )
236
237
238class Postgres(Dialect):
239    INDEX_OFFSET = 1
240    TYPED_DIVISION = True
241    CONCAT_COALESCE = True
242    NULL_ORDERING = "nulls_are_large"
243    TIME_FORMAT = "'YYYY-MM-DD HH24:MI:SS'"
244    TABLESAMPLE_SIZE_IS_PERCENT = True
245
246    TIME_MAPPING = {
247        "AM": "%p",
248        "PM": "%p",
249        "D": "%u",  # 1-based day of week
250        "DD": "%d",  # day of month
251        "DDD": "%j",  # zero padded day of year
252        "FMDD": "%-d",  # - is no leading zero for Python; same for FM in postgres
253        "FMDDD": "%-j",  # day of year
254        "FMHH12": "%-I",  # 9
255        "FMHH24": "%-H",  # 9
256        "FMMI": "%-M",  # Minute
257        "FMMM": "%-m",  # 1
258        "FMSS": "%-S",  # Second
259        "HH12": "%I",  # 09
260        "HH24": "%H",  # 09
261        "MI": "%M",  # zero padded minute
262        "MM": "%m",  # 01
263        "OF": "%z",  # utc offset
264        "SS": "%S",  # zero padded second
265        "TMDay": "%A",  # TM is locale dependent
266        "TMDy": "%a",
267        "TMMon": "%b",  # Sep
268        "TMMonth": "%B",  # September
269        "TZ": "%Z",  # uppercase timezone name
270        "US": "%f",  # zero padded microsecond
271        "WW": "%U",  # 1-based week of year
272        "YY": "%y",  # 15
273        "YYYY": "%Y",  # 2015
274    }
275
276    class Tokenizer(tokens.Tokenizer):
277        BIT_STRINGS = [("b'", "'"), ("B'", "'")]
278        HEX_STRINGS = [("x'", "'"), ("X'", "'")]
279        BYTE_STRINGS = [("e'", "'"), ("E'", "'")]
280        HEREDOC_STRINGS = ["$"]
281
282        HEREDOC_TAG_IS_IDENTIFIER = True
283        HEREDOC_STRING_ALTERNATIVE = TokenType.PARAMETER
284
285        KEYWORDS = {
286            **tokens.Tokenizer.KEYWORDS,
287            "~~": TokenType.LIKE,
288            "~~*": TokenType.ILIKE,
289            "~*": TokenType.IRLIKE,
290            "~": TokenType.RLIKE,
291            "@@": TokenType.DAT,
292            "@>": TokenType.AT_GT,
293            "<@": TokenType.LT_AT,
294            "|/": TokenType.PIPE_SLASH,
295            "||/": TokenType.DPIPE_SLASH,
296            "BEGIN": TokenType.COMMAND,
297            "BEGIN TRANSACTION": TokenType.BEGIN,
298            "BIGSERIAL": TokenType.BIGSERIAL,
299            "CHARACTER VARYING": TokenType.VARCHAR,
300            "CONSTRAINT TRIGGER": TokenType.COMMAND,
301            "CSTRING": TokenType.PSEUDO_TYPE,
302            "DECLARE": TokenType.COMMAND,
303            "DO": TokenType.COMMAND,
304            "EXEC": TokenType.COMMAND,
305            "HSTORE": TokenType.HSTORE,
306            "INT8": TokenType.BIGINT,
307            "MONEY": TokenType.MONEY,
308            "NAME": TokenType.NAME,
309            "OID": TokenType.OBJECT_IDENTIFIER,
310            "ONLY": TokenType.ONLY,
311            "OPERATOR": TokenType.OPERATOR,
312            "REFRESH": TokenType.COMMAND,
313            "REINDEX": TokenType.COMMAND,
314            "RESET": TokenType.COMMAND,
315            "REVOKE": TokenType.COMMAND,
316            "SERIAL": TokenType.SERIAL,
317            "SMALLSERIAL": TokenType.SMALLSERIAL,
318            "TEMP": TokenType.TEMPORARY,
319            "REGCLASS": TokenType.OBJECT_IDENTIFIER,
320            "REGCOLLATION": TokenType.OBJECT_IDENTIFIER,
321            "REGCONFIG": TokenType.OBJECT_IDENTIFIER,
322            "REGDICTIONARY": TokenType.OBJECT_IDENTIFIER,
323            "REGNAMESPACE": TokenType.OBJECT_IDENTIFIER,
324            "REGOPER": TokenType.OBJECT_IDENTIFIER,
325            "REGOPERATOR": TokenType.OBJECT_IDENTIFIER,
326            "REGPROC": TokenType.OBJECT_IDENTIFIER,
327            "REGPROCEDURE": TokenType.OBJECT_IDENTIFIER,
328            "REGROLE": TokenType.OBJECT_IDENTIFIER,
329            "REGTYPE": TokenType.OBJECT_IDENTIFIER,
330            "FLOAT": TokenType.DOUBLE,
331        }
332
333        SINGLE_TOKENS = {
334            **tokens.Tokenizer.SINGLE_TOKENS,
335            "$": TokenType.HEREDOC_STRING,
336        }
337
338        VAR_SINGLE_TOKENS = {"$"}
339
340    class Parser(parser.Parser):
341        PROPERTY_PARSERS = {
342            **parser.Parser.PROPERTY_PARSERS,
343            "SET": lambda self: self.expression(exp.SetConfigProperty, this=self._parse_set()),
344        }
345        PROPERTY_PARSERS.pop("INPUT")
346
347        FUNCTIONS = {
348            **parser.Parser.FUNCTIONS,
349            "DATE_TRUNC": build_timestamp_trunc,
350            "GENERATE_SERIES": _build_generate_series,
351            "JSON_EXTRACT_PATH": build_json_extract_path(exp.JSONExtract),
352            "JSON_EXTRACT_PATH_TEXT": build_json_extract_path(exp.JSONExtractScalar),
353            "MAKE_TIME": exp.TimeFromParts.from_arg_list,
354            "MAKE_TIMESTAMP": exp.TimestampFromParts.from_arg_list,
355            "NOW": exp.CurrentTimestamp.from_arg_list,
356            "REGEXP_REPLACE": _build_regexp_replace,
357            "TO_CHAR": build_formatted_time(exp.TimeToStr, "postgres"),
358            "TO_TIMESTAMP": _build_to_timestamp,
359            "UNNEST": exp.Explode.from_arg_list,
360        }
361
362        FUNCTION_PARSERS = {
363            **parser.Parser.FUNCTION_PARSERS,
364            "DATE_PART": lambda self: self._parse_date_part(),
365        }
366
367        BITWISE = {
368            **parser.Parser.BITWISE,
369            TokenType.HASH: exp.BitwiseXor,
370        }
371
372        EXPONENT = {
373            TokenType.CARET: exp.Pow,
374        }
375
376        RANGE_PARSERS = {
377            **parser.Parser.RANGE_PARSERS,
378            TokenType.AT_GT: binary_range_parser(exp.ArrayContainsAll),
379            TokenType.DAMP: binary_range_parser(exp.ArrayOverlaps),
380            TokenType.DAT: lambda self, this: self.expression(
381                exp.MatchAgainst, this=self._parse_bitwise(), expressions=[this]
382            ),
383            TokenType.LT_AT: binary_range_parser(exp.ArrayContainsAll, reverse_args=True),
384            TokenType.OPERATOR: lambda self, this: self._parse_operator(this),
385        }
386
387        STATEMENT_PARSERS = {
388            **parser.Parser.STATEMENT_PARSERS,
389            TokenType.END: lambda self: self._parse_commit_or_rollback(),
390        }
391
392        JSON_ARROWS_REQUIRE_JSON_TYPE = True
393
394        COLUMN_OPERATORS = {
395            **parser.Parser.COLUMN_OPERATORS,
396            TokenType.ARROW: lambda self, this, path: build_json_extract_path(
397                exp.JSONExtract, arrow_req_json_type=self.JSON_ARROWS_REQUIRE_JSON_TYPE
398            )([this, path]),
399            TokenType.DARROW: lambda self, this, path: build_json_extract_path(
400                exp.JSONExtractScalar, arrow_req_json_type=self.JSON_ARROWS_REQUIRE_JSON_TYPE
401            )([this, path]),
402        }
403
404        def _parse_operator(self, this: t.Optional[exp.Expression]) -> t.Optional[exp.Expression]:
405            while True:
406                if not self._match(TokenType.L_PAREN):
407                    break
408
409                op = ""
410                while self._curr and not self._match(TokenType.R_PAREN):
411                    op += self._curr.text
412                    self._advance()
413
414                this = self.expression(
415                    exp.Operator,
416                    comments=self._prev_comments,
417                    this=this,
418                    operator=op,
419                    expression=self._parse_bitwise(),
420                )
421
422                if not self._match(TokenType.OPERATOR):
423                    break
424
425            return this
426
427        def _parse_date_part(self) -> exp.Expression:
428            part = self._parse_type()
429            self._match(TokenType.COMMA)
430            value = self._parse_bitwise()
431
432            if part and part.is_string:
433                part = exp.var(part.name)
434
435            return self.expression(exp.Extract, this=part, expression=value)
436
437    class Generator(generator.Generator):
438        SINGLE_STRING_INTERVAL = True
439        RENAME_TABLE_WITH_DB = False
440        LOCKING_READS_SUPPORTED = True
441        JOIN_HINTS = False
442        TABLE_HINTS = False
443        QUERY_HINTS = False
444        NVL2_SUPPORTED = False
445        PARAMETER_TOKEN = "$"
446        TABLESAMPLE_SIZE_IS_ROWS = False
447        TABLESAMPLE_SEED_KEYWORD = "REPEATABLE"
448        SUPPORTS_SELECT_INTO = True
449        JSON_TYPE_REQUIRED_FOR_EXTRACTION = True
450        SUPPORTS_UNLOGGED_TABLES = True
451        LIKE_PROPERTY_INSIDE_SCHEMA = True
452        MULTI_ARG_DISTINCT = False
453        CAN_IMPLEMENT_ARRAY_ANY = True
454        COPY_HAS_INTO_KEYWORD = False
455
456        SUPPORTED_JSON_PATH_PARTS = {
457            exp.JSONPathKey,
458            exp.JSONPathRoot,
459            exp.JSONPathSubscript,
460        }
461
462        TYPE_MAPPING = {
463            **generator.Generator.TYPE_MAPPING,
464            exp.DataType.Type.TINYINT: "SMALLINT",
465            exp.DataType.Type.FLOAT: "REAL",
466            exp.DataType.Type.DOUBLE: "DOUBLE PRECISION",
467            exp.DataType.Type.BINARY: "BYTEA",
468            exp.DataType.Type.VARBINARY: "BYTEA",
469            exp.DataType.Type.ROWVERSION: "BYTEA",
470            exp.DataType.Type.DATETIME: "TIMESTAMP",
471        }
472
473        TRANSFORMS = {
474            **generator.Generator.TRANSFORMS,
475            exp.AnyValue: any_value_to_max_sql,
476            exp.Array: lambda self, e: (
477                f"{self.normalize_func('ARRAY')}({self.sql(e.expressions[0])})"
478                if isinstance(seq_get(e.expressions, 0), exp.Select)
479                else f"{self.normalize_func('ARRAY')}[{self.expressions(e, flat=True)}]"
480            ),
481            exp.ArrayConcat: rename_func("ARRAY_CAT"),
482            exp.ArrayContainsAll: lambda self, e: self.binary(e, "@>"),
483            exp.ArrayOverlaps: lambda self, e: self.binary(e, "&&"),
484            exp.ArrayFilter: filter_array_using_unnest,
485            exp.ArraySize: lambda self, e: self.func("ARRAY_LENGTH", e.this, e.expression or "1"),
486            exp.BitwiseXor: lambda self, e: self.binary(e, "#"),
487            exp.ColumnDef: transforms.preprocess([_auto_increment_to_serial, _serial_to_generated]),
488            exp.CurrentDate: no_paren_current_date_sql,
489            exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
490            exp.CurrentUser: lambda *_: "CURRENT_USER",
491            exp.DateAdd: _date_add_sql("+"),
492            exp.DateDiff: _date_diff_sql,
493            exp.DateStrToDate: datestrtodate_sql,
494            exp.DateSub: _date_add_sql("-"),
495            exp.Explode: rename_func("UNNEST"),
496            exp.GroupConcat: _string_agg_sql,
497            exp.JSONExtract: _json_extract_sql("JSON_EXTRACT_PATH", "->"),
498            exp.JSONExtractScalar: _json_extract_sql("JSON_EXTRACT_PATH_TEXT", "->>"),
499            exp.JSONBExtract: lambda self, e: self.binary(e, "#>"),
500            exp.JSONBExtractScalar: lambda self, e: self.binary(e, "#>>"),
501            exp.JSONBContains: lambda self, e: self.binary(e, "?"),
502            exp.ParseJSON: lambda self, e: self.sql(exp.cast(e.this, exp.DataType.Type.JSON)),
503            exp.JSONPathKey: json_path_key_only_name,
504            exp.JSONPathRoot: lambda *_: "",
505            exp.JSONPathSubscript: lambda self, e: self.json_path_part(e.this),
506            exp.LastDay: no_last_day_sql,
507            exp.LogicalOr: rename_func("BOOL_OR"),
508            exp.LogicalAnd: rename_func("BOOL_AND"),
509            exp.Max: max_or_greatest,
510            exp.MapFromEntries: no_map_from_entries_sql,
511            exp.Min: min_or_least,
512            exp.Merge: merge_without_target_sql,
513            exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
514            exp.PercentileCont: transforms.preprocess(
515                [transforms.add_within_group_for_percentiles]
516            ),
517            exp.PercentileDisc: transforms.preprocess(
518                [transforms.add_within_group_for_percentiles]
519            ),
520            exp.Pivot: no_pivot_sql,
521            exp.Pow: lambda self, e: self.binary(e, "^"),
522            exp.Rand: rename_func("RANDOM"),
523            exp.RegexpLike: lambda self, e: self.binary(e, "~"),
524            exp.RegexpILike: lambda self, e: self.binary(e, "~*"),
525            exp.Select: transforms.preprocess(
526                [
527                    transforms.eliminate_semi_and_anti_joins,
528                    transforms.eliminate_qualify,
529                ]
530            ),
531            exp.StrPosition: str_position_sql,
532            exp.StrToDate: lambda self, e: self.func("TO_DATE", e.this, self.format_time(e)),
533            exp.StrToTime: lambda self, e: self.func("TO_TIMESTAMP", e.this, self.format_time(e)),
534            exp.StructExtract: struct_extract_sql,
535            exp.Substring: _substring_sql,
536            exp.TimeFromParts: rename_func("MAKE_TIME"),
537            exp.TimestampFromParts: rename_func("MAKE_TIMESTAMP"),
538            exp.TimestampTrunc: timestamptrunc_sql(zone=True),
539            exp.TimeStrToTime: timestrtotime_sql,
540            exp.TimeToStr: lambda self, e: self.func("TO_CHAR", e.this, self.format_time(e)),
541            exp.ToChar: lambda self, e: self.function_fallback_sql(e),
542            exp.Trim: trim_sql,
543            exp.TryCast: no_trycast_sql,
544            exp.TsOrDsAdd: _date_add_sql("+"),
545            exp.TsOrDsDiff: _date_diff_sql,
546            exp.UnixToTime: lambda self, e: self.func("TO_TIMESTAMP", e.this),
547            exp.TimeToUnix: lambda self, e: self.func(
548                "DATE_PART", exp.Literal.string("epoch"), e.this
549            ),
550            exp.VariancePop: rename_func("VAR_POP"),
551            exp.Variance: rename_func("VAR_SAMP"),
552            exp.Xor: bool_xor_sql,
553            exp.UnixToTime: _unix_to_time_sql,
554        }
555        TRANSFORMS.pop(exp.CommentColumnConstraint)
556
557        PROPERTIES_LOCATION = {
558            **generator.Generator.PROPERTIES_LOCATION,
559            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
560            exp.TransientProperty: exp.Properties.Location.UNSUPPORTED,
561            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
562        }
563
564        def schemacommentproperty_sql(self, expression: exp.SchemaCommentProperty) -> str:
565            self.unsupported("Table comments are not supported in the CREATE statement")
566            return ""
567
568        def commentcolumnconstraint_sql(self, expression: exp.CommentColumnConstraint) -> str:
569            self.unsupported("Column comments are not supported in the CREATE statement")
570            return ""
571
572        def unnest_sql(self, expression: exp.Unnest) -> str:
573            if len(expression.expressions) == 1:
574                from sqlglot.optimizer.annotate_types import annotate_types
575
576                this = annotate_types(expression.expressions[0])
577                if this.is_type("array<json>"):
578                    while isinstance(this, exp.Cast):
579                        this = this.this
580
581                    arg = self.sql(exp.cast(this, exp.DataType.Type.JSON))
582                    alias = self.sql(expression, "alias")
583                    alias = f" AS {alias}" if alias else ""
584
585                    if expression.args.get("offset"):
586                        self.unsupported("Unsupported JSON_ARRAY_ELEMENTS with offset")
587
588                    return f"JSON_ARRAY_ELEMENTS({arg}){alias}"
589
590            return super().unnest_sql(expression)
591
592        def bracket_sql(self, expression: exp.Bracket) -> str:
593            """Forms like ARRAY[1, 2, 3][3] aren't allowed; we need to wrap the ARRAY."""
594            if isinstance(expression.this, exp.Array):
595                expression.set("this", exp.paren(expression.this, copy=False))
596
597            return super().bracket_sql(expression)
598
599        def matchagainst_sql(self, expression: exp.MatchAgainst) -> str:
600            this = self.sql(expression, "this")
601            expressions = [f"{self.sql(e)} @@ {this}" for e in expression.expressions]
602            sql = " OR ".join(expressions)
603            return f"({sql})" if len(expressions) > 1 else sql
604
605        def alterset_sql(self, expression: exp.AlterSet) -> str:
606            exprs = self.expressions(expression, flat=True)
607            exprs = f"({exprs})" if exprs else ""
608
609            access_method = self.sql(expression, "access_method")
610            access_method = f"ACCESS METHOD {access_method}" if access_method else ""
611            tablespace = self.sql(expression, "tablespace")
612            tablespace = f"TABLESPACE {tablespace}" if tablespace else ""
613            option = self.sql(expression, "option")
614
615            return f"SET {exprs}{access_method}{tablespace}{option}"
616
617        def datatype_sql(self, expression: exp.DataType) -> str:
618            if expression.is_type(exp.DataType.Type.ARRAY):
619                if expression.expressions:
620                    values = self.expressions(expression, key="values", flat=True)
621                    return f"{self.expressions(expression, flat=True)}[{values}]"
622                return "ARRAY"
623            return super().datatype_sql(expression)