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