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    array_append_sql,
  12    array_concat_sql,
  13    binary_from_function,
  14    bool_xor_sql,
  15    datestrtodate_sql,
  16    build_formatted_time,
  17    filter_array_using_unnest,
  18    getbit_sql,
  19    inline_array_sql,
  20    json_extract_segments,
  21    json_path_key_only_name,
  22    max_or_greatest,
  23    merge_without_target_sql,
  24    min_or_least,
  25    no_last_day_sql,
  26    no_map_from_entries_sql,
  27    no_paren_current_date_sql,
  28    no_pivot_sql,
  29    no_trycast_sql,
  30    build_json_extract_path,
  31    build_timestamp_trunc,
  32    rename_func,
  33    sha256_sql,
  34    struct_extract_sql,
  35    timestamptrunc_sql,
  36    timestrtotime_sql,
  37    trim_sql,
  38    ts_or_ds_add_cast,
  39    strposition_sql,
  40    count_if_to_sum,
  41    groupconcat_sql,
  42    regexp_replace_global_modifier,
  43    sha2_digest_sql,
  44)
  45from sqlglot.generator import unsupported_args
  46from sqlglot.helper import is_int, seq_get
  47from sqlglot.parser import binary_range_parser
  48from sqlglot.tokens import TokenType
  49
  50if t.TYPE_CHECKING:
  51    from sqlglot.dialects.dialect import DialectType
  52
  53
  54DATE_DIFF_FACTOR = {
  55    "MICROSECOND": " * 1000000",
  56    "MILLISECOND": " * 1000",
  57    "SECOND": "",
  58    "MINUTE": " / 60",
  59    "HOUR": " / 3600",
  60    "DAY": " / 86400",
  61}
  62
  63
  64def _date_add_sql(kind: str) -> t.Callable[[Postgres.Generator, DATE_ADD_OR_SUB], str]:
  65    def func(self: Postgres.Generator, expression: DATE_ADD_OR_SUB) -> str:
  66        if isinstance(expression, exp.TsOrDsAdd):
  67            expression = ts_or_ds_add_cast(expression)
  68
  69        this = self.sql(expression, "this")
  70        unit = expression.args.get("unit")
  71
  72        e = self._simplify_unless_literal(expression.expression)
  73        if isinstance(e, exp.Literal):
  74            e.set("is_string", True)
  75        elif e.is_number:
  76            e = exp.Literal.string(e.to_py())
  77        else:
  78            self.unsupported("Cannot add non literal")
  79
  80        return f"{this} {kind} {self.sql(exp.Interval(this=e, unit=unit))}"
  81
  82    return func
  83
  84
  85def _date_diff_sql(self: Postgres.Generator, expression: exp.DateDiff) -> str:
  86    unit = expression.text("unit").upper()
  87    factor = DATE_DIFF_FACTOR.get(unit)
  88
  89    end = f"CAST({self.sql(expression, 'this')} AS TIMESTAMP)"
  90    start = f"CAST({self.sql(expression, 'expression')} AS TIMESTAMP)"
  91
  92    if factor is not None:
  93        return f"CAST(EXTRACT(epoch FROM {end} - {start}){factor} AS BIGINT)"
  94
  95    age = f"AGE({end}, {start})"
  96
  97    if unit == "WEEK":
  98        unit = f"EXTRACT(days FROM ({end} - {start})) / 7"
  99    elif unit == "MONTH":
 100        unit = f"EXTRACT(year FROM {age}) * 12 + EXTRACT(month FROM {age})"
 101    elif unit == "QUARTER":
 102        unit = f"EXTRACT(year FROM {age}) * 4 + EXTRACT(month FROM {age}) / 3"
 103    elif unit == "YEAR":
 104        unit = f"EXTRACT(year FROM {age})"
 105    else:
 106        unit = age
 107
 108    return f"CAST({unit} AS BIGINT)"
 109
 110
 111def _substring_sql(self: Postgres.Generator, expression: exp.Substring) -> str:
 112    this = self.sql(expression, "this")
 113    start = self.sql(expression, "start")
 114    length = self.sql(expression, "length")
 115
 116    from_part = f" FROM {start}" if start else ""
 117    for_part = f" FOR {length}" if length else ""
 118
 119    return f"SUBSTRING({this}{from_part}{for_part})"
 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, dialect: DialectType = None) -> 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    regexp_replace = None
 211    if len(args) > 3:
 212        last = args[-1]
 213        if not is_int(last.name):
 214            if not last.type or last.is_type(exp.DataType.Type.UNKNOWN, exp.DataType.Type.NULL):
 215                from sqlglot.optimizer.annotate_types import annotate_types
 216
 217                last = annotate_types(last, dialect=dialect)
 218
 219            if last.is_type(*exp.DataType.TEXT_TYPES):
 220                regexp_replace = exp.RegexpReplace.from_arg_list(args[:-1])
 221                regexp_replace.set("modifiers", last)
 222
 223    regexp_replace = regexp_replace or exp.RegexpReplace.from_arg_list(args)
 224    regexp_replace.set("single_replace", True)
 225    return regexp_replace
 226
 227
 228def _unix_to_time_sql(self: Postgres.Generator, expression: exp.UnixToTime) -> str:
 229    scale = expression.args.get("scale")
 230    timestamp = expression.this
 231
 232    if scale in (None, exp.UnixToTime.SECONDS):
 233        return self.func("TO_TIMESTAMP", timestamp, self.format_time(expression))
 234
 235    return self.func(
 236        "TO_TIMESTAMP",
 237        exp.Div(this=timestamp, expression=exp.func("POW", 10, scale)),
 238        self.format_time(expression),
 239    )
 240
 241
 242def _build_levenshtein_less_equal(args: t.List) -> exp.Levenshtein:
 243    # Postgres has two signatures for levenshtein_less_equal function, but in both cases
 244    # max_dist is the last argument
 245    # levenshtein_less_equal(source, target, ins_cost, del_cost, sub_cost, max_d)
 246    # levenshtein_less_equal(source, target, max_d)
 247    max_dist = args.pop()
 248
 249    return exp.Levenshtein(
 250        this=seq_get(args, 0),
 251        expression=seq_get(args, 1),
 252        ins_cost=seq_get(args, 2),
 253        del_cost=seq_get(args, 3),
 254        sub_cost=seq_get(args, 4),
 255        max_dist=max_dist,
 256    )
 257
 258
 259def _levenshtein_sql(self: Postgres.Generator, expression: exp.Levenshtein) -> str:
 260    name = "LEVENSHTEIN_LESS_EQUAL" if expression.args.get("max_dist") else "LEVENSHTEIN"
 261
 262    return rename_func(name)(self, expression)
 263
 264
 265def _versioned_anyvalue_sql(self: Postgres.Generator, expression: exp.AnyValue) -> str:
 266    # https://www.postgresql.org/docs/16/functions-aggregate.html
 267    # https://www.postgresql.org/about/featurematrix/
 268    if self.dialect.version < (16,):
 269        return any_value_to_max_sql(self, expression)
 270
 271    return rename_func("ANY_VALUE")(self, expression)
 272
 273
 274def _round_sql(self: Postgres.Generator, expression: exp.Round) -> str:
 275    this = self.sql(expression, "this")
 276    decimals = self.sql(expression, "decimals")
 277
 278    if not decimals:
 279        return self.func("ROUND", this)
 280
 281    if not expression.type:
 282        from sqlglot.optimizer.annotate_types import annotate_types
 283
 284        expression = annotate_types(expression, dialect=self.dialect)
 285
 286    # ROUND(double precision, integer) is not permitted in Postgres
 287    # so it's necessary to cast to decimal before rounding.
 288    if expression.this.is_type(exp.DataType.Type.DOUBLE):
 289        decimal_type = exp.DataType.build(
 290            exp.DataType.Type.DECIMAL, expressions=expression.expressions
 291        )
 292        this = self.sql(exp.Cast(this=this, to=decimal_type))
 293
 294    return self.func("ROUND", this, decimals)
 295
 296
 297class Postgres(Dialect):
 298    INDEX_OFFSET = 1
 299    TYPED_DIVISION = True
 300    CONCAT_COALESCE = True
 301    NULL_ORDERING = "nulls_are_large"
 302    TIME_FORMAT = "'YYYY-MM-DD HH24:MI:SS'"
 303    TABLESAMPLE_SIZE_IS_PERCENT = True
 304    TABLES_REFERENCEABLE_AS_COLUMNS = True
 305
 306    DEFAULT_FUNCTIONS_COLUMN_NAMES = {
 307        exp.ExplodingGenerateSeries: "generate_series",
 308    }
 309
 310    TIME_MAPPING = {
 311        "d": "%u",  # 1-based day of week
 312        "D": "%u",  # 1-based day of week
 313        "dd": "%d",  # day of month
 314        "DD": "%d",  # day of month
 315        "ddd": "%j",  # zero padded day of year
 316        "DDD": "%j",  # zero padded day of year
 317        "FMDD": "%-d",  # - is no leading zero for Python; same for FM in postgres
 318        "FMDDD": "%-j",  # day of year
 319        "FMHH12": "%-I",  # 9
 320        "FMHH24": "%-H",  # 9
 321        "FMMI": "%-M",  # Minute
 322        "FMMM": "%-m",  # 1
 323        "FMSS": "%-S",  # Second
 324        "HH12": "%I",  # 09
 325        "HH24": "%H",  # 09
 326        "mi": "%M",  # zero padded minute
 327        "MI": "%M",  # zero padded minute
 328        "mm": "%m",  # 01
 329        "MM": "%m",  # 01
 330        "OF": "%z",  # utc offset
 331        "ss": "%S",  # zero padded second
 332        "SS": "%S",  # zero padded second
 333        "TMDay": "%A",  # TM is locale dependent
 334        "TMDy": "%a",
 335        "TMMon": "%b",  # Sep
 336        "TMMonth": "%B",  # September
 337        "TZ": "%Z",  # uppercase timezone name
 338        "US": "%f",  # zero padded microsecond
 339        "ww": "%U",  # 1-based week of year
 340        "WW": "%U",  # 1-based week of year
 341        "yy": "%y",  # 15
 342        "YY": "%y",  # 15
 343        "yyyy": "%Y",  # 2015
 344        "YYYY": "%Y",  # 2015
 345    }
 346
 347    class Tokenizer(tokens.Tokenizer):
 348        BIT_STRINGS = [("b'", "'"), ("B'", "'")]
 349        HEX_STRINGS = [("x'", "'"), ("X'", "'")]
 350        BYTE_STRINGS = [("e'", "'"), ("E'", "'")]
 351        BYTE_STRING_ESCAPES = ["'", "\\"]
 352        HEREDOC_STRINGS = ["$"]
 353
 354        HEREDOC_TAG_IS_IDENTIFIER = True
 355        HEREDOC_STRING_ALTERNATIVE = TokenType.PARAMETER
 356
 357        KEYWORDS = {
 358            **tokens.Tokenizer.KEYWORDS,
 359            "~": TokenType.RLIKE,
 360            "@@": TokenType.DAT,
 361            "@>": TokenType.AT_GT,
 362            "<@": TokenType.LT_AT,
 363            "?&": TokenType.QMARK_AMP,
 364            "?|": TokenType.QMARK_PIPE,
 365            "#-": TokenType.HASH_DASH,
 366            "|/": TokenType.PIPE_SLASH,
 367            "||/": TokenType.DPIPE_SLASH,
 368            "BEGIN": TokenType.BEGIN,
 369            "BIGSERIAL": TokenType.BIGSERIAL,
 370            "CONSTRAINT TRIGGER": TokenType.COMMAND,
 371            "CSTRING": TokenType.PSEUDO_TYPE,
 372            "DECLARE": TokenType.COMMAND,
 373            "DO": TokenType.COMMAND,
 374            "EXEC": TokenType.COMMAND,
 375            "HSTORE": TokenType.HSTORE,
 376            "INT8": TokenType.BIGINT,
 377            "MONEY": TokenType.MONEY,
 378            "NAME": TokenType.NAME,
 379            "OID": TokenType.OBJECT_IDENTIFIER,
 380            "ONLY": TokenType.ONLY,
 381            "POINT": TokenType.POINT,
 382            "REFRESH": TokenType.COMMAND,
 383            "REINDEX": TokenType.COMMAND,
 384            "RESET": TokenType.COMMAND,
 385            "SERIAL": TokenType.SERIAL,
 386            "SMALLSERIAL": TokenType.SMALLSERIAL,
 387            "TEMP": TokenType.TEMPORARY,
 388            "REGCLASS": TokenType.OBJECT_IDENTIFIER,
 389            "REGCOLLATION": TokenType.OBJECT_IDENTIFIER,
 390            "REGCONFIG": TokenType.OBJECT_IDENTIFIER,
 391            "REGDICTIONARY": TokenType.OBJECT_IDENTIFIER,
 392            "REGNAMESPACE": TokenType.OBJECT_IDENTIFIER,
 393            "REGOPER": TokenType.OBJECT_IDENTIFIER,
 394            "REGOPERATOR": TokenType.OBJECT_IDENTIFIER,
 395            "REGPROC": TokenType.OBJECT_IDENTIFIER,
 396            "REGPROCEDURE": TokenType.OBJECT_IDENTIFIER,
 397            "REGROLE": TokenType.OBJECT_IDENTIFIER,
 398            "REGTYPE": TokenType.OBJECT_IDENTIFIER,
 399            "FLOAT": TokenType.DOUBLE,
 400            "XML": TokenType.XML,
 401            "VARIADIC": TokenType.VARIADIC,
 402            "INOUT": TokenType.INOUT,
 403        }
 404        KEYWORDS.pop("/*+")
 405        KEYWORDS.pop("DIV")
 406
 407        SINGLE_TOKENS = {
 408            **tokens.Tokenizer.SINGLE_TOKENS,
 409            "$": TokenType.HEREDOC_STRING,
 410        }
 411
 412        VAR_SINGLE_TOKENS = {"$"}
 413
 414    class Parser(parser.Parser):
 415        SUPPORTS_OMITTED_INTERVAL_SPAN_UNIT = True
 416
 417        PROPERTY_PARSERS = {
 418            **parser.Parser.PROPERTY_PARSERS,
 419            "SET": lambda self: self.expression(exp.SetConfigProperty, this=self._parse_set()),
 420        }
 421        PROPERTY_PARSERS.pop("INPUT")
 422
 423        PLACEHOLDER_PARSERS = {
 424            **parser.Parser.PLACEHOLDER_PARSERS,
 425            TokenType.PLACEHOLDER: lambda self: self.expression(exp.Placeholder, jdbc=True),
 426            TokenType.MOD: lambda self: self._parse_query_parameter(),
 427        }
 428
 429        FUNCTIONS = {
 430            **parser.Parser.FUNCTIONS,
 431            "ARRAY_PREPEND": lambda args: exp.ArrayPrepend(
 432                this=seq_get(args, 1), expression=seq_get(args, 0)
 433            ),
 434            "BIT_AND": exp.BitwiseAndAgg.from_arg_list,
 435            "BIT_OR": exp.BitwiseOrAgg.from_arg_list,
 436            "BIT_XOR": exp.BitwiseXorAgg.from_arg_list,
 437            "DATE_TRUNC": build_timestamp_trunc,
 438            "DIV": lambda args: exp.cast(
 439                binary_from_function(exp.IntDiv)(args), exp.DataType.Type.DECIMAL
 440            ),
 441            "GENERATE_SERIES": _build_generate_series,
 442            "GET_BIT": lambda args: exp.Getbit(
 443                this=seq_get(args, 0), expression=seq_get(args, 1), zero_is_msb=True
 444            ),
 445            "JSON_EXTRACT_PATH": build_json_extract_path(exp.JSONExtract),
 446            "JSON_EXTRACT_PATH_TEXT": build_json_extract_path(exp.JSONExtractScalar),
 447            "LENGTH": lambda args: exp.Length(this=seq_get(args, 0), encoding=seq_get(args, 1)),
 448            "MAKE_TIME": exp.TimeFromParts.from_arg_list,
 449            "MAKE_TIMESTAMP": exp.TimestampFromParts.from_arg_list,
 450            "NOW": exp.CurrentTimestamp.from_arg_list,
 451            "REGEXP_REPLACE": _build_regexp_replace,
 452            "TO_CHAR": build_formatted_time(exp.TimeToStr, "postgres"),
 453            "TO_DATE": build_formatted_time(exp.StrToDate, "postgres"),
 454            "TO_TIMESTAMP": _build_to_timestamp,
 455            "UNNEST": exp.Explode.from_arg_list,
 456            "SHA256": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(256)),
 457            "SHA384": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(384)),
 458            "SHA512": lambda args: exp.SHA2(this=seq_get(args, 0), length=exp.Literal.number(512)),
 459            "LEVENSHTEIN_LESS_EQUAL": _build_levenshtein_less_equal,
 460            "JSON_OBJECT_AGG": lambda args: exp.JSONObjectAgg(expressions=args),
 461            "JSONB_OBJECT_AGG": exp.JSONBObjectAgg.from_arg_list,
 462            "WIDTH_BUCKET": lambda args: exp.WidthBucket(
 463                this=seq_get(args, 0), threshold=seq_get(args, 1)
 464            )
 465            if len(args) == 2
 466            else exp.WidthBucket.from_arg_list(args),
 467        }
 468
 469        NO_PAREN_FUNCTION_PARSERS = {
 470            **parser.Parser.NO_PAREN_FUNCTION_PARSERS,
 471            "VARIADIC": lambda self: self.expression(exp.Variadic, this=self._parse_bitwise()),
 472        }
 473
 474        NO_PAREN_FUNCTIONS = {
 475            **parser.Parser.NO_PAREN_FUNCTIONS,
 476            TokenType.CURRENT_SCHEMA: exp.CurrentSchema,
 477        }
 478
 479        FUNCTION_PARSERS = {
 480            **parser.Parser.FUNCTION_PARSERS,
 481            "DATE_PART": lambda self: self._parse_date_part(),
 482            "JSON_AGG": lambda self: self.expression(
 483                exp.JSONArrayAgg,
 484                this=self._parse_lambda(),
 485                order=self._parse_order(),
 486            ),
 487            "JSONB_EXISTS": lambda self: self._parse_jsonb_exists(),
 488        }
 489
 490        BITWISE = {
 491            **parser.Parser.BITWISE,
 492            TokenType.HASH: exp.BitwiseXor,
 493        }
 494
 495        EXPONENT = {
 496            TokenType.CARET: exp.Pow,
 497        }
 498
 499        RANGE_PARSERS = {
 500            **parser.Parser.RANGE_PARSERS,
 501            TokenType.DAMP: binary_range_parser(exp.ArrayOverlaps),
 502            TokenType.DAT: lambda self, this: self.expression(
 503                exp.MatchAgainst, this=self._parse_bitwise(), expressions=[this]
 504            ),
 505        }
 506
 507        STATEMENT_PARSERS = {
 508            **parser.Parser.STATEMENT_PARSERS,
 509            TokenType.END: lambda self: self._parse_commit_or_rollback(),
 510        }
 511
 512        UNARY_PARSERS = {
 513            **parser.Parser.UNARY_PARSERS,
 514            # The `~` token is remapped from TILDE to RLIKE in Postgres due to the binary REGEXP LIKE operator
 515            TokenType.RLIKE: lambda self: self.expression(exp.BitwiseNot, this=self._parse_unary()),
 516        }
 517
 518        JSON_ARROWS_REQUIRE_JSON_TYPE = True
 519
 520        COLUMN_OPERATORS = {
 521            **parser.Parser.COLUMN_OPERATORS,
 522            TokenType.ARROW: lambda self, this, path: self.validate_expression(
 523                build_json_extract_path(
 524                    exp.JSONExtract, arrow_req_json_type=self.JSON_ARROWS_REQUIRE_JSON_TYPE
 525                )([this, path])
 526            ),
 527            TokenType.DARROW: lambda self, this, path: self.validate_expression(
 528                build_json_extract_path(
 529                    exp.JSONExtractScalar, arrow_req_json_type=self.JSON_ARROWS_REQUIRE_JSON_TYPE
 530                )([this, path])
 531            ),
 532        }
 533
 534        ARG_MODE_TOKENS = {TokenType.IN, TokenType.OUT, TokenType.INOUT, TokenType.VARIADIC}
 535
 536        def _parse_parameter_mode(self) -> t.Optional[TokenType]:
 537            """
 538            Parse PostgreSQL function parameter mode (IN, OUT, INOUT, VARIADIC).
 539
 540            Disambiguates between mode keywords and identifiers with the same name:
 541            - MODE TYPE      → keyword is identifier (e.g., "out INT")
 542            - MODE NAME TYPE → keyword is mode (e.g., "OUT x INT")
 543
 544            Returns:
 545                Mode token type if current token is a mode keyword, None otherwise.
 546            """
 547            if not self._match_set(self.ARG_MODE_TOKENS, advance=False) or not self._next:
 548                return None
 549
 550            mode_token = self._curr
 551
 552            # Check Pattern 1: MODE TYPE
 553            # Try parsing next token as a built-in type (not UDT)
 554            # If successful, the keyword is an identifier, not a mode
 555            is_followed_by_builtin_type = self._try_parse(
 556                lambda: self._advance()  # type: ignore
 557                or self._parse_types(check_func=False, allow_identifiers=False),
 558                retreat=True,
 559            )
 560            if is_followed_by_builtin_type:
 561                return None  # Pattern: "out INT" → out is parameter name
 562
 563            # Check Pattern 2: MODE NAME TYPE
 564            # If next token is an identifier, check if there's a type after it
 565            # The type can be built-in or user-defined (allow_identifiers=True)
 566            if self._next.token_type not in self.ID_VAR_TOKENS:
 567                return None
 568
 569            is_followed_by_any_type = self._try_parse(
 570                lambda: self._advance(2)  # type: ignore
 571                or self._parse_types(check_func=False, allow_identifiers=True),
 572                retreat=True,
 573            )
 574
 575            if is_followed_by_any_type:
 576                return mode_token.token_type  # Pattern: "OUT x INT" → OUT is mode
 577
 578            return None
 579
 580        def _create_mode_constraint(self, param_mode: TokenType) -> exp.InOutColumnConstraint:
 581            """
 582            Create parameter mode constraint for function parameters.
 583
 584            Args:
 585                param_mode: The parameter mode token (IN, OUT, INOUT, or VARIADIC).
 586
 587            Returns:
 588                InOutColumnConstraint expression representing the parameter mode.
 589            """
 590            return self.expression(
 591                exp.InOutColumnConstraint,
 592                input_=(param_mode in {TokenType.IN, TokenType.INOUT}),
 593                output=(param_mode in {TokenType.OUT, TokenType.INOUT}),
 594                variadic=(param_mode == TokenType.VARIADIC),
 595            )
 596
 597        def _parse_function_parameter(self) -> t.Optional[exp.Expression]:
 598            param_mode = self._parse_parameter_mode()
 599
 600            if param_mode:
 601                self._advance()
 602
 603            # Parse parameter name and type
 604            param_name = self._parse_id_var()
 605            column_def = self._parse_column_def(this=param_name, computed_column=False)
 606
 607            # Attach mode as constraint
 608            if param_mode and column_def:
 609                constraint = self._create_mode_constraint(param_mode)
 610                if not column_def.args.get("constraints"):
 611                    column_def.set("constraints", [])
 612                column_def.args["constraints"].insert(0, constraint)
 613
 614            return column_def
 615
 616        def _parse_query_parameter(self) -> t.Optional[exp.Expression]:
 617            this = (
 618                self._parse_wrapped(self._parse_id_var)
 619                if self._match(TokenType.L_PAREN, advance=False)
 620                else None
 621            )
 622            self._match_text_seq("S")
 623            return self.expression(exp.Placeholder, this=this)
 624
 625        def _parse_date_part(self) -> exp.Expression:
 626            part = self._parse_type()
 627            self._match(TokenType.COMMA)
 628            value = self._parse_bitwise()
 629
 630            if part and isinstance(part, (exp.Column, exp.Literal)):
 631                part = exp.var(part.name)
 632
 633            return self.expression(exp.Extract, this=part, expression=value)
 634
 635        def _parse_unique_key(self) -> t.Optional[exp.Expression]:
 636            return None
 637
 638        def _parse_jsonb_exists(self) -> exp.JSONBExists:
 639            return self.expression(
 640                exp.JSONBExists,
 641                this=self._parse_bitwise(),
 642                path=self._match(TokenType.COMMA)
 643                and self.dialect.to_json_path(self._parse_bitwise()),
 644            )
 645
 646        def _parse_generated_as_identity(
 647            self,
 648        ) -> (
 649            exp.GeneratedAsIdentityColumnConstraint
 650            | exp.ComputedColumnConstraint
 651            | exp.GeneratedAsRowColumnConstraint
 652        ):
 653            this = super()._parse_generated_as_identity()
 654
 655            if self._match_text_seq("STORED"):
 656                this = self.expression(exp.ComputedColumnConstraint, this=this.expression)
 657
 658            return this
 659
 660        def _parse_user_defined_type(
 661            self, identifier: exp.Identifier
 662        ) -> t.Optional[exp.Expression]:
 663            udt_type: exp.Identifier | exp.Dot = identifier
 664
 665            while self._match(TokenType.DOT):
 666                part = self._parse_id_var()
 667                if part:
 668                    udt_type = exp.Dot(this=udt_type, expression=part)
 669
 670            return exp.DataType.build(udt_type, udt=True)
 671
 672    class Generator(generator.Generator):
 673        SINGLE_STRING_INTERVAL = True
 674        RENAME_TABLE_WITH_DB = False
 675        LOCKING_READS_SUPPORTED = True
 676        JOIN_HINTS = False
 677        TABLE_HINTS = False
 678        QUERY_HINTS = False
 679        NVL2_SUPPORTED = False
 680        PARAMETER_TOKEN = "$"
 681        NAMED_PLACEHOLDER_TOKEN = "%"
 682        TABLESAMPLE_SIZE_IS_ROWS = False
 683        TABLESAMPLE_SEED_KEYWORD = "REPEATABLE"
 684        SUPPORTS_SELECT_INTO = True
 685        JSON_TYPE_REQUIRED_FOR_EXTRACTION = True
 686        SUPPORTS_UNLOGGED_TABLES = True
 687        LIKE_PROPERTY_INSIDE_SCHEMA = True
 688        MULTI_ARG_DISTINCT = False
 689        CAN_IMPLEMENT_ARRAY_ANY = True
 690        SUPPORTS_WINDOW_EXCLUDE = True
 691        COPY_HAS_INTO_KEYWORD = False
 692        ARRAY_CONCAT_IS_VAR_LEN = False
 693        SUPPORTS_MEDIAN = False
 694        ARRAY_SIZE_DIM_REQUIRED = True
 695        SUPPORTS_BETWEEN_FLAGS = True
 696        INOUT_SEPARATOR = ""  # PostgreSQL uses "INOUT" (no space)
 697
 698        SUPPORTED_JSON_PATH_PARTS = {
 699            exp.JSONPathKey,
 700            exp.JSONPathRoot,
 701            exp.JSONPathSubscript,
 702        }
 703
 704        def lateral_sql(self, expression: exp.Lateral) -> str:
 705            sql = super().lateral_sql(expression)
 706
 707            if expression.args.get("cross_apply") is not None:
 708                sql = f"{sql} ON TRUE"
 709
 710            return sql
 711
 712        TYPE_MAPPING = {
 713            **generator.Generator.TYPE_MAPPING,
 714            exp.DataType.Type.TINYINT: "SMALLINT",
 715            exp.DataType.Type.FLOAT: "REAL",
 716            exp.DataType.Type.DOUBLE: "DOUBLE PRECISION",
 717            exp.DataType.Type.BINARY: "BYTEA",
 718            exp.DataType.Type.VARBINARY: "BYTEA",
 719            exp.DataType.Type.ROWVERSION: "BYTEA",
 720            exp.DataType.Type.DATETIME: "TIMESTAMP",
 721            exp.DataType.Type.TIMESTAMPNTZ: "TIMESTAMP",
 722            exp.DataType.Type.BLOB: "BYTEA",
 723        }
 724
 725        TRANSFORMS = {
 726            **generator.Generator.TRANSFORMS,
 727            exp.AnyValue: _versioned_anyvalue_sql,
 728            exp.ArrayConcat: array_concat_sql("ARRAY_CAT"),
 729            exp.ArrayFilter: filter_array_using_unnest,
 730            exp.ArrayAppend: array_append_sql("ARRAY_APPEND"),
 731            exp.ArrayPrepend: array_append_sql("ARRAY_PREPEND", swap_params=True),
 732            exp.BitwiseAndAgg: rename_func("BIT_AND"),
 733            exp.BitwiseOrAgg: rename_func("BIT_OR"),
 734            exp.BitwiseXor: lambda self, e: self.binary(e, "#"),
 735            exp.BitwiseXorAgg: rename_func("BIT_XOR"),
 736            exp.ColumnDef: transforms.preprocess([_auto_increment_to_serial, _serial_to_generated]),
 737            exp.CurrentDate: no_paren_current_date_sql,
 738            exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
 739            exp.CurrentUser: lambda *_: "CURRENT_USER",
 740            exp.DateAdd: _date_add_sql("+"),
 741            exp.DateDiff: _date_diff_sql,
 742            exp.DateStrToDate: datestrtodate_sql,
 743            exp.DateSub: _date_add_sql("-"),
 744            exp.Explode: rename_func("UNNEST"),
 745            exp.ExplodingGenerateSeries: rename_func("GENERATE_SERIES"),
 746            exp.Getbit: getbit_sql,
 747            exp.GroupConcat: lambda self, e: groupconcat_sql(
 748                self, e, func_name="STRING_AGG", within_group=False
 749            ),
 750            exp.IntDiv: rename_func("DIV"),
 751            exp.JSONArrayAgg: lambda self, e: self.func(
 752                "JSON_AGG",
 753                self.sql(e, "this"),
 754                suffix=f"{self.sql(e, 'order')})",
 755            ),
 756            exp.JSONExtract: _json_extract_sql("JSON_EXTRACT_PATH", "->"),
 757            exp.JSONExtractScalar: _json_extract_sql("JSON_EXTRACT_PATH_TEXT", "->>"),
 758            exp.JSONBExtract: lambda self, e: self.binary(e, "#>"),
 759            exp.JSONBExtractScalar: lambda self, e: self.binary(e, "#>>"),
 760            exp.JSONBContains: lambda self, e: self.binary(e, "?"),
 761            exp.ParseJSON: lambda self, e: self.sql(exp.cast(e.this, exp.DataType.Type.JSON)),
 762            exp.JSONPathKey: json_path_key_only_name,
 763            exp.JSONPathRoot: lambda *_: "",
 764            exp.JSONPathSubscript: lambda self, e: self.json_path_part(e.this),
 765            exp.LastDay: no_last_day_sql,
 766            exp.LogicalOr: rename_func("BOOL_OR"),
 767            exp.LogicalAnd: rename_func("BOOL_AND"),
 768            exp.Max: max_or_greatest,
 769            exp.MapFromEntries: no_map_from_entries_sql,
 770            exp.Min: min_or_least,
 771            exp.Merge: merge_without_target_sql,
 772            exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
 773            exp.PercentileCont: transforms.preprocess(
 774                [transforms.add_within_group_for_percentiles]
 775            ),
 776            exp.PercentileDisc: transforms.preprocess(
 777                [transforms.add_within_group_for_percentiles]
 778            ),
 779            exp.Pivot: no_pivot_sql,
 780            exp.Rand: rename_func("RANDOM"),
 781            exp.RegexpLike: lambda self, e: self.binary(e, "~"),
 782            exp.RegexpILike: lambda self, e: self.binary(e, "~*"),
 783            exp.RegexpReplace: lambda self, e: self.func(
 784                "REGEXP_REPLACE",
 785                e.this,
 786                e.expression,
 787                e.args.get("replacement"),
 788                e.args.get("position"),
 789                e.args.get("occurrence"),
 790                regexp_replace_global_modifier(e),
 791            ),
 792            exp.Round: _round_sql,
 793            exp.Select: transforms.preprocess(
 794                [
 795                    transforms.eliminate_semi_and_anti_joins,
 796                    transforms.eliminate_qualify,
 797                ]
 798            ),
 799            exp.SHA2: sha256_sql,
 800            exp.SHA2Digest: sha2_digest_sql,
 801            exp.StrPosition: lambda self, e: strposition_sql(self, e, func_name="POSITION"),
 802            exp.StrToDate: lambda self, e: self.func("TO_DATE", e.this, self.format_time(e)),
 803            exp.StrToTime: lambda self, e: self.func("TO_TIMESTAMP", e.this, self.format_time(e)),
 804            exp.StructExtract: struct_extract_sql,
 805            exp.Substring: _substring_sql,
 806            exp.TimeFromParts: rename_func("MAKE_TIME"),
 807            exp.TimestampFromParts: rename_func("MAKE_TIMESTAMP"),
 808            exp.TimestampTrunc: timestamptrunc_sql(zone=True),
 809            exp.TimeStrToTime: timestrtotime_sql,
 810            exp.TimeToStr: lambda self, e: self.func("TO_CHAR", e.this, self.format_time(e)),
 811            exp.ToChar: lambda self, e: self.function_fallback_sql(e)
 812            if e.args.get("format")
 813            else self.tochar_sql(e),
 814            exp.Trim: trim_sql,
 815            exp.TryCast: no_trycast_sql,
 816            exp.TsOrDsAdd: _date_add_sql("+"),
 817            exp.TsOrDsDiff: _date_diff_sql,
 818            exp.UnixToTime: lambda self, e: self.func("TO_TIMESTAMP", e.this),
 819            exp.Uuid: lambda *_: "GEN_RANDOM_UUID()",
 820            exp.TimeToUnix: lambda self, e: self.func(
 821                "DATE_PART", exp.Literal.string("epoch"), e.this
 822            ),
 823            exp.VariancePop: rename_func("VAR_POP"),
 824            exp.Variance: rename_func("VAR_SAMP"),
 825            exp.Xor: bool_xor_sql,
 826            exp.Unicode: rename_func("ASCII"),
 827            exp.UnixToTime: _unix_to_time_sql,
 828            exp.Levenshtein: _levenshtein_sql,
 829            exp.JSONObjectAgg: rename_func("JSON_OBJECT_AGG"),
 830            exp.JSONBObjectAgg: rename_func("JSONB_OBJECT_AGG"),
 831            exp.CountIf: count_if_to_sum,
 832        }
 833
 834        TRANSFORMS.pop(exp.CommentColumnConstraint)
 835
 836        PROPERTIES_LOCATION = {
 837            **generator.Generator.PROPERTIES_LOCATION,
 838            exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
 839            exp.TransientProperty: exp.Properties.Location.UNSUPPORTED,
 840            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
 841        }
 842
 843        def schemacommentproperty_sql(self, expression: exp.SchemaCommentProperty) -> str:
 844            self.unsupported("Table comments are not supported in the CREATE statement")
 845            return ""
 846
 847        def commentcolumnconstraint_sql(self, expression: exp.CommentColumnConstraint) -> str:
 848            self.unsupported("Column comments are not supported in the CREATE statement")
 849            return ""
 850
 851        def columndef_sql(self, expression: exp.ColumnDef, sep: str = " ") -> str:
 852            # PostgreSQL places parameter modes BEFORE parameter name
 853            param_constraint = expression.find(exp.InOutColumnConstraint)
 854
 855            if param_constraint:
 856                mode_sql = self.sql(param_constraint)
 857                param_constraint.pop()  # Remove to prevent double-rendering
 858                base_sql = super().columndef_sql(expression, sep)
 859                return f"{mode_sql} {base_sql}"
 860
 861            return super().columndef_sql(expression, sep)
 862
 863        def unnest_sql(self, expression: exp.Unnest) -> str:
 864            if len(expression.expressions) == 1:
 865                arg = expression.expressions[0]
 866                if isinstance(arg, exp.GenerateDateArray):
 867                    generate_series: exp.Expression = exp.GenerateSeries(**arg.args)
 868                    if isinstance(expression.parent, (exp.From, exp.Join)):
 869                        generate_series = (
 870                            exp.select("value::date")
 871                            .from_(exp.Table(this=generate_series).as_("_t", table=["value"]))
 872                            .subquery(expression.args.get("alias") or "_unnested_generate_series")
 873                        )
 874                    return self.sql(generate_series)
 875
 876                from sqlglot.optimizer.annotate_types import annotate_types
 877
 878                this = annotate_types(arg, dialect=self.dialect)
 879                if this.is_type("array<json>"):
 880                    while isinstance(this, exp.Cast):
 881                        this = this.this
 882
 883                    arg_as_json = self.sql(exp.cast(this, exp.DataType.Type.JSON))
 884                    alias = self.sql(expression, "alias")
 885                    alias = f" AS {alias}" if alias else ""
 886
 887                    if expression.args.get("offset"):
 888                        self.unsupported("Unsupported JSON_ARRAY_ELEMENTS with offset")
 889
 890                    return f"JSON_ARRAY_ELEMENTS({arg_as_json}){alias}"
 891
 892            return super().unnest_sql(expression)
 893
 894        def bracket_sql(self, expression: exp.Bracket) -> str:
 895            """Forms like ARRAY[1, 2, 3][3] aren't allowed; we need to wrap the ARRAY."""
 896            if isinstance(expression.this, exp.Array):
 897                expression.set("this", exp.paren(expression.this, copy=False))
 898
 899            return super().bracket_sql(expression)
 900
 901        def matchagainst_sql(self, expression: exp.MatchAgainst) -> str:
 902            this = self.sql(expression, "this")
 903            expressions = [f"{self.sql(e)} @@ {this}" for e in expression.expressions]
 904            sql = " OR ".join(expressions)
 905            return f"({sql})" if len(expressions) > 1 else sql
 906
 907        def alterset_sql(self, expression: exp.AlterSet) -> str:
 908            exprs = self.expressions(expression, flat=True)
 909            exprs = f"({exprs})" if exprs else ""
 910
 911            access_method = self.sql(expression, "access_method")
 912            access_method = f"ACCESS METHOD {access_method}" if access_method else ""
 913            tablespace = self.sql(expression, "tablespace")
 914            tablespace = f"TABLESPACE {tablespace}" if tablespace else ""
 915            option = self.sql(expression, "option")
 916
 917            return f"SET {exprs}{access_method}{tablespace}{option}"
 918
 919        def datatype_sql(self, expression: exp.DataType) -> str:
 920            if expression.is_type(exp.DataType.Type.ARRAY):
 921                if expression.expressions:
 922                    values = self.expressions(expression, key="values", flat=True)
 923                    return f"{self.expressions(expression, flat=True)}[{values}]"
 924                return "ARRAY"
 925
 926            if (
 927                expression.is_type(exp.DataType.Type.DOUBLE, exp.DataType.Type.FLOAT)
 928                and expression.expressions
 929            ):
 930                # Postgres doesn't support precision for REAL and DOUBLE PRECISION types
 931                return f"FLOAT({self.expressions(expression, flat=True)})"
 932
 933            return super().datatype_sql(expression)
 934
 935        def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
 936            this = expression.this
 937
 938            # Postgres casts DIV() to decimal for transpilation but when roundtripping it's superfluous
 939            if isinstance(this, exp.IntDiv) and expression.to == exp.DataType.build("decimal"):
 940                return self.sql(this)
 941
 942            return super().cast_sql(expression, safe_prefix=safe_prefix)
 943
 944        def array_sql(self, expression: exp.Array) -> str:
 945            exprs = expression.expressions
 946            func_name = self.normalize_func("ARRAY")
 947
 948            if isinstance(seq_get(exprs, 0), exp.Select):
 949                return f"{func_name}({self.sql(exprs[0])})"
 950
 951            return f"{func_name}{inline_array_sql(self, expression)}"
 952
 953        def computedcolumnconstraint_sql(self, expression: exp.ComputedColumnConstraint) -> str:
 954            return f"GENERATED ALWAYS AS ({self.sql(expression, 'this')}) STORED"
 955
 956        def isascii_sql(self, expression: exp.IsAscii) -> str:
 957            return f"({self.sql(expression.this)} ~ '^[[:ascii:]]*$')"
 958
 959        def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str:
 960            # https://www.postgresql.org/docs/current/functions-window.html
 961            self.unsupported("PostgreSQL does not support IGNORE NULLS.")
 962            return self.sql(expression.this)
 963
 964        def respectnulls_sql(self, expression: exp.RespectNulls) -> str:
 965            # https://www.postgresql.org/docs/current/functions-window.html
 966            self.unsupported("PostgreSQL does not support RESPECT NULLS.")
 967            return self.sql(expression.this)
 968
 969        @unsupported_args("this")
 970        def currentschema_sql(self, expression: exp.CurrentSchema) -> str:
 971            return "CURRENT_SCHEMA"
 972
 973        def interval_sql(self, expression: exp.Interval) -> str:
 974            unit = expression.text("unit").lower()
 975
 976            this = expression.this
 977            if unit.startswith("quarter") and isinstance(this, exp.Literal):
 978                this.replace(exp.Literal.string(int(this.to_py()) * 3))
 979                expression.args["unit"].replace(exp.var("MONTH"))
 980
 981            return super().interval_sql(expression)
 982
 983        def placeholder_sql(self, expression: exp.Placeholder) -> str:
 984            if expression.args.get("jdbc"):
 985                return "?"
 986
 987            this = f"({expression.name})" if expression.this else ""
 988            return f"{self.NAMED_PLACEHOLDER_TOKEN}{this}s"
 989
 990        def arraycontains_sql(self, expression: exp.ArrayContains) -> str:
 991            # Convert DuckDB's LIST_CONTAINS(array, value) to PostgreSQL
 992            # DuckDB behavior:
 993            #   - LIST_CONTAINS([1,2,3], 2) -> true
 994            #   - LIST_CONTAINS([1,2,3], 4) -> false
 995            #   - LIST_CONTAINS([1,2,NULL], 4) -> false (not NULL)
 996            #   - LIST_CONTAINS([1,2,3], NULL) -> NULL
 997            #
 998            # PostgreSQL equivalent: CASE WHEN value IS NULL THEN NULL
 999            #                            ELSE COALESCE(value = ANY(array), FALSE) END
1000            value = expression.expression
1001            array = expression.this
1002
1003            coalesce_expr = exp.Coalesce(
1004                this=value.eq(exp.Any(this=exp.paren(expression=array, copy=False))),
1005                expressions=[exp.false()],
1006            )
1007
1008            case_expr = (
1009                exp.Case()
1010                .when(exp.Is(this=value, expression=exp.null()), exp.null(), copy=False)
1011                .else_(coalesce_expr, copy=False)
1012            )
1013
1014            return self.sql(case_expr)