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