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