sqlglot.generators.postgres

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, generator, transforms
  6from sqlglot.dialects.dialect import (
  7    DATE_ADD_OR_SUB,
  8    JSON_EXTRACT_TYPE,
  9    any_value_to_max_sql,
 10    arrow_json_extract_sql,
 11    array_append_sql,
 12    array_concat_sql,
 13    bool_xor_sql,
 14    count_if_to_sum,
 15    date_delta_to_binary_sql,
 16    datestrtodate_sql,
 17    filter_array_using_unnest,
 18    generate_series_sql,
 19    getbit_sql,
 20    groupconcat_sql,
 21    inline_array_sql,
 22    json_extract_segments,
 23    json_path_key_only_name,
 24    max_or_greatest,
 25    merge_without_target_sql,
 26    min_or_least,
 27    no_last_day_sql,
 28    no_map_from_entries_sql,
 29    no_paren_current_date_sql,
 30    no_pivot_sql,
 31    no_trycast_sql,
 32    regexp_replace_global_modifier,
 33    rename_func,
 34    sha256_sql,
 35    sha2_digest_sql,
 36    strposition_sql,
 37    struct_extract_sql,
 38    timestamptrunc_sql,
 39    timestrtotime_sql,
 40    trim_sql,
 41    ts_or_ds_add_cast,
 42)
 43from sqlglot.generator import unsupported_args
 44from sqlglot.helper import ensure_list, seq_get, to_int
 45
 46
 47DATE_DIFF_FACTOR = {
 48    "MICROSECOND": " * 1000000",
 49    "MILLISECOND": " * 1000",
 50    "SECOND": "",
 51    "MINUTE": " / 60",
 52    "HOUR": " / 3600",
 53    "DAY": " / 86400",
 54}
 55
 56DATE_DIFF_BOUNDARY_UNITS = {
 57    "MICROSECOND",
 58    "MILLISECOND",
 59    "SECOND",
 60    "MINUTE",
 61    "HOUR",
 62    "MONTH",
 63    "QUARTER",
 64    "YEAR",
 65}
 66
 67
 68def _date_add_sql(kind: str) -> t.Callable[[PostgresGenerator, DATE_ADD_OR_SUB], str]:
 69    def func(self: PostgresGenerator, expression: DATE_ADD_OR_SUB) -> str:
 70        if isinstance(expression, exp.TsOrDsAdd):
 71            expression = ts_or_ds_add_cast(expression)
 72
 73        this = expression.this
 74        unit = expression.args.get("unit")
 75
 76        e = self._simplify_unless_literal(expression.expression)
 77        interval: exp.Expr
 78        if isinstance(e, exp.Interval):
 79            interval = e
 80        elif e.is_number or e.is_string:
 81            interval = exp.Interval(this=exp.Literal.string(e.to_py()), unit=unit)
 82        else:
 83            one = exp.Literal.number(1)
 84            interval = exp.Interval(this=one, unit=unit) * e
 85
 86        return date_delta_to_binary_sql(
 87            self, expression, this + interval if kind == "+" else this - interval
 88        )
 89
 90    return func
 91
 92
 93def _day_month_year_sql(self: PostgresGenerator, expression: exp.Day | exp.Month | exp.Year) -> str:
 94    this = expression.this
 95    value = this.this if isinstance(this, exp.TsOrDsToDate) else this
 96
 97    if value.is_type(*exp.DataType.INTEGER_TYPES) and (
 98        default_date := this.args.get("default_date")
 99    ):
100        this = exp.cast(default_date, exp.DType.DATE) + value
101
102    return self.sql(exp.Extract(this=exp.var(expression.sql_name()), expression=this))
103
104
105def _date_diff_sql(self: PostgresGenerator, expression: exp.DateDiff | exp.TsOrDsDiff) -> str:
106    unit = expression.text("unit").upper() or "DAY"
107
108    # Dialects like MySQL count crossed day boundaries, which maps to DATE subtraction
109    if unit == "DAY" and expression.args.get("date_part_boundary"):
110        this = exp.cast(expression.this, exp.DType.DATE)
111        expr = exp.cast(expression.expression, exp.DType.DATE)
112        return self.sql(exp.paren(this - expr))
113
114    factor = DATE_DIFF_FACTOR.get(unit)
115
116    end = f"CAST({self.sql(expression, 'this')} AS TIMESTAMP)"
117    start = f"CAST({self.sql(expression, 'expression')} AS TIMESTAMP)"
118
119    # The arithmetic below counts complete units between the operands, whereas dialects that set
120    # date_part_boundary count unit boundary crossings, so the operands are truncated down to the
121    # unit to make the two coincide. WEEK is left out: its start day differs across dialects
122    if expression.args.get("date_part_boundary") and unit in DATE_DIFF_BOUNDARY_UNITS:
123        end = self.func("DATE_TRUNC", exp.Literal.string(unit), end)
124        start = self.func("DATE_TRUNC", exp.Literal.string(unit), start)
125
126    if factor is not None:
127        return f"CAST(EXTRACT(epoch FROM {end} - {start}){factor} AS BIGINT)"
128
129    age = f"AGE({end}, {start})"
130
131    if unit == "WEEK":
132        unit = f"EXTRACT(days FROM ({end} - {start})) / 7"
133    elif unit == "MONTH":
134        unit = f"EXTRACT(year FROM {age}) * 12 + EXTRACT(month FROM {age})"
135    elif unit == "QUARTER":
136        unit = f"EXTRACT(year FROM {age}) * 4 + EXTRACT(month FROM {age}) / 3"
137    elif unit == "YEAR":
138        unit = f"EXTRACT(year FROM {age})"
139    else:
140        unit = age
141
142    return f"CAST({unit} AS BIGINT)"
143
144
145def _substring_sql(self: PostgresGenerator, expression: exp.Substring) -> str:
146    this = self.sql(expression, "this")
147    start = self.sql(expression, "start")
148    length = self.sql(expression, "length")
149
150    from_part = f" FROM {start}" if start else ""
151    for_part = f" FOR {length}" if length else ""
152
153    return f"SUBSTRING({this}{from_part}{for_part})"
154
155
156def _auto_increment_to_serial(expression: exp.Expr) -> exp.Expr:
157    auto = expression.find(exp.AutoIncrementColumnConstraint)
158
159    if auto:
160        expression.args["constraints"].remove(auto.parent)
161        kind = expression.args["kind"]
162
163        if kind.this == exp.DType.INT:
164            kind.replace(exp.DataType(this=exp.DType.SERIAL))
165        elif kind.this == exp.DType.SMALLINT:
166            kind.replace(exp.DataType(this=exp.DType.SMALLSERIAL))
167        elif kind.this == exp.DType.BIGINT:
168            kind.replace(exp.DataType(this=exp.DType.BIGSERIAL))
169
170    return expression
171
172
173def _serial_to_generated(expression: exp.Expr) -> exp.Expr:
174    if not isinstance(expression, exp.ColumnDef):
175        return expression
176    kind = expression.kind
177    if not kind:
178        return expression
179
180    if kind.this == exp.DType.SERIAL:
181        data_type = exp.DataType(this=exp.DType.INT)
182    elif kind.this == exp.DType.SMALLSERIAL:
183        data_type = exp.DataType(this=exp.DType.SMALLINT)
184    elif kind.this == exp.DType.BIGSERIAL:
185        data_type = exp.DataType(this=exp.DType.BIGINT)
186    else:
187        data_type = None
188
189    if data_type:
190        expression.args["kind"].replace(data_type)
191        constraints = expression.args["constraints"]
192        generated = exp.ColumnConstraint(kind=exp.GeneratedAsIdentityColumnConstraint(this=False))
193        notnull = exp.ColumnConstraint(kind=exp.NotNullColumnConstraint())
194
195        if notnull not in constraints:
196            constraints.insert(0, notnull)
197        if generated not in constraints:
198            constraints.insert(0, generated)
199
200    return expression
201
202
203def _json_extract_sql(
204    name: str, op: str
205) -> t.Callable[[PostgresGenerator, JSON_EXTRACT_TYPE], str]:
206    def _generate(self: PostgresGenerator, expression: JSON_EXTRACT_TYPE) -> str:
207        path = expression.expression
208        # Single non-literal segment: render as infix, not JSON_EXTRACT_PATH[_TEXT] (jsonb-unsafe).
209        if not isinstance(path, (exp.JSONPath, exp.Variadic)) and not ensure_list(
210            expression.args.get("expressions")
211        ):
212            return arrow_json_extract_sql(self, expression, op=op)
213
214        # JSON_EXTRACT_PATH requires a key, so use an empty variadic array for the root path
215        if len(path.expressions) == 1 and isinstance(path.expressions[0], exp.JSONPathRoot):
216            expression.set("expression", exp.Variadic(this=exp.Literal.string("{}")))
217
218        if expression.args.get("only_json_types"):
219            return json_extract_segments(name, quoted_index=False, op=op)(self, expression)
220        return json_extract_segments(name)(self, expression)
221
222    return _generate
223
224
225def _unix_to_time_sql(self: PostgresGenerator, expression: exp.UnixToTime) -> str:
226    scale = expression.args.get("scale")
227    timestamp = expression.this
228
229    if scale in (None, exp.UnixToTime.SECONDS):
230        return self.func("TO_TIMESTAMP", timestamp, self.format_time(expression))
231
232    return self.func(
233        "TO_TIMESTAMP",
234        exp.Div(this=timestamp, expression=exp.func("POW", 10, scale)),
235        self.format_time(expression),
236    )
237
238
239def _levenshtein_sql(self: PostgresGenerator, expression: exp.Levenshtein) -> str:
240    name = "LEVENSHTEIN_LESS_EQUAL" if expression.args.get("max_dist") else "LEVENSHTEIN"
241
242    return rename_func(name)(self, expression)
243
244
245def _versioned_anyvalue_sql(self: PostgresGenerator, expression: exp.AnyValue) -> str:
246    # https://www.postgresql.org/docs/16/functions-aggregate.html
247    # https://www.postgresql.org/about/featurematrix/
248    if self.dialect.version < (16,):
249        return any_value_to_max_sql(self, expression)
250
251    return rename_func("ANY_VALUE")(self, expression)
252
253
254def _round_sql(self: PostgresGenerator, expression: exp.Round) -> str:
255    this = self.sql(expression, "this")
256    decimals = self.sql(expression, "decimals")
257
258    if not decimals:
259        return self.func("ROUND", this)
260
261    if not expression.type:
262        from sqlglot.optimizer.annotate_types import annotate_types
263
264        expression = annotate_types(expression, dialect=self.dialect)
265
266    # ROUND(double precision, integer) is not permitted in Postgres
267    # so it's necessary to cast to decimal before rounding.
268    if expression.this.is_type(exp.DType.DOUBLE):
269        decimal_type = exp.DType.DECIMAL.into_expr(expressions=expression.expressions)
270        this = self.sql(exp.Cast(this=this, to=decimal_type))
271
272    return self.func("ROUND", this, decimals)
273
274
275class PostgresGenerator(generator.Generator):
276    SELECT_KINDS: tuple[str, ...] = ()
277    TRY_SUPPORTED = False
278    SUPPORTS_DECODE_CASE = False
279
280    AFTER_HAVING_MODIFIER_TRANSFORMS = generator.AFTER_HAVING_MODIFIER_TRANSFORMS
281
282    SINGLE_STRING_INTERVAL = True
283    RENAME_TABLE_WITH_DB = False
284    LOCKING_READS_SUPPORTED = True
285    JOIN_HINTS = False
286    TABLE_HINTS = False
287    QUERY_HINTS = False
288    NVL2_SUPPORTED = False
289    PARAMETER_TOKEN = "$"
290    NAMED_PLACEHOLDER_TOKEN = "%"
291    TABLESAMPLE_SIZE_IS_ROWS = False
292    TABLESAMPLE_SEED_KEYWORD = "REPEATABLE"
293    SUPPORTS_SELECT_INTO = True
294    JSON_TYPE_REQUIRED_FOR_EXTRACTION = True
295    SUPPORTS_UNLOGGED_TABLES = True
296    LIKE_PROPERTY_INSIDE_SCHEMA = True
297    MULTI_ARG_DISTINCT = False
298    CAN_IMPLEMENT_ARRAY_ANY = True
299    SUPPORTS_WINDOW_EXCLUDE = True
300    COPY_HAS_INTO_KEYWORD = False
301    ARRAY_CONCAT_IS_VAR_LEN = False
302    SUPPORTS_MEDIAN = False
303    ARRAY_SIZE_DIM_REQUIRED: bool | None = True
304    SUPPORTS_BETWEEN_FLAGS = True
305    INOUT_SEPARATOR = ""  # PostgreSQL uses "INOUT" (no space)
306
307    SUPPORTED_JSON_PATH_PARTS = {
308        exp.JSONPathKey,
309        exp.JSONPathRoot,
310        exp.JSONPathSubscript,
311    }
312
313    def lateral_sql(self, expression: exp.Lateral) -> str:
314        sql = super().lateral_sql(expression)
315
316        if expression.args.get("cross_apply") is not None:
317            sql = f"{sql} ON TRUE"
318
319        return sql
320
321    def star_sql(self, expression: exp.Star) -> str:
322        if self.STAR_EXCEPT == "EXCEPT" and expression.args.get("except_"):
323            self.unsupported("PostgreSQL does not support EXCEPT in the SELECT projection")
324
325        return super().star_sql(expression)
326
327    TYPE_MAPPING = {
328        **generator.Generator.TYPE_MAPPING,
329        exp.DType.TINYINT: "SMALLINT",
330        exp.DType.FLOAT: "REAL",
331        exp.DType.DOUBLE: "DOUBLE PRECISION",
332        exp.DType.BINARY: "BYTEA",
333        exp.DType.VARBINARY: "BYTEA",
334        exp.DType.ROWVERSION: "BYTEA",
335        exp.DType.DATETIME: "TIMESTAMP",
336        exp.DType.TIMESTAMPNTZ: "TIMESTAMP",
337        exp.DType.BLOB: "BYTEA",
338    }
339
340    TRANSFORMS = {
341        **{
342            k: v
343            for k, v in generator.Generator.TRANSFORMS.items()
344            if k != exp.CommentColumnConstraint
345        },
346        exp.AnyValue: _versioned_anyvalue_sql,
347        exp.ArrayConcat: array_concat_sql("ARRAY_CAT"),
348        exp.ArrayFilter: filter_array_using_unnest,
349        exp.ArrayAppend: array_append_sql("ARRAY_APPEND"),
350        exp.ArrayPrepend: array_append_sql("ARRAY_PREPEND", swap_params=True),
351        exp.BitwiseAndAgg: rename_func("BIT_AND"),
352        exp.BitwiseOrAgg: rename_func("BIT_OR"),
353        exp.BitwiseXor: lambda self, e: self.binary(e, "#"),
354        exp.BitwiseXorAgg: rename_func("BIT_XOR"),
355        exp.ColumnDef: transforms.preprocess([_auto_increment_to_serial, _serial_to_generated]),
356        exp.CurrentDate: no_paren_current_date_sql,
357        exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
358        exp.CurrentUser: lambda *_: "CURRENT_USER",
359        exp.CurrentVersion: rename_func("VERSION"),
360        exp.DateAdd: _date_add_sql("+"),
361        exp.DateDiff: _date_diff_sql,
362        exp.DateStrToDate: datestrtodate_sql,
363        exp.DateSub: _date_add_sql("-"),
364        exp.DatetimeAdd: _date_add_sql("+"),
365        exp.DatetimeSub: _date_add_sql("-"),
366        exp.Day: _day_month_year_sql,
367        exp.Explode: rename_func("UNNEST"),
368        exp.ExplodingGenerateSeries: rename_func("GENERATE_SERIES"),
369        exp.GenerateSeries: generate_series_sql("GENERATE_SERIES"),
370        exp.Getbit: getbit_sql,
371        exp.GroupConcat: lambda self, e: groupconcat_sql(
372            self, e, func_name="STRING_AGG", within_group=False
373        ),
374        exp.IntDiv: rename_func("DIV"),
375        exp.JSONArrayAgg: lambda self, e: self.func(
376            "JSON_AGG",
377            self.sql(e, "this"),
378            suffix=f"{self.sql(e, 'order')})",
379        ),
380        exp.JSONExtract: _json_extract_sql("JSON_EXTRACT_PATH", "->"),
381        exp.JSONExtractScalar: _json_extract_sql("JSON_EXTRACT_PATH_TEXT", "->>"),
382        exp.JSONBExtract: lambda self, e: arrow_json_extract_sql(self, e, op="#>"),
383        exp.JSONBExtractScalar: lambda self, e: arrow_json_extract_sql(self, e, op="#>>"),
384        exp.ParseJSON: lambda self, e: self.sql(exp.cast(e.this, exp.DType.JSON)),
385        exp.JSONPathKey: json_path_key_only_name,
386        exp.JSONPathRoot: lambda *_: "",
387        exp.JSONPathSubscript: lambda self, e: self.json_path_part(e.this),
388        exp.LastDay: no_last_day_sql,
389        exp.LogicalOr: rename_func("BOOL_OR"),
390        exp.LogicalAnd: rename_func("BOOL_AND"),
391        exp.Max: max_or_greatest,
392        exp.MapFromEntries: no_map_from_entries_sql,
393        exp.Min: min_or_least,
394        exp.Merge: merge_without_target_sql,
395        exp.Month: _day_month_year_sql,
396        exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
397        exp.PercentileCont: transforms.preprocess([transforms.add_within_group_for_percentiles]),
398        exp.PercentileDisc: transforms.preprocess([transforms.add_within_group_for_percentiles]),
399        exp.Pivot: no_pivot_sql,
400        exp.Rand: rename_func("RANDOM"),
401        exp.RegexpILike: lambda self, e: self.binary(e, "~*"),
402        exp.RegexpReplace: lambda self, e: self.func(
403            "REGEXP_REPLACE",
404            e.this,
405            e.expression,
406            e.args.get("replacement") or exp.Literal.string(""),
407            e.args.get("position"),
408            e.args.get("occurrence"),
409            regexp_replace_global_modifier(e),
410        ),
411        exp.Round: _round_sql,
412        exp.Select: transforms.preprocess(
413            [
414                transforms.eliminate_semi_and_anti_joins,
415                transforms.eliminate_qualify,
416            ]
417        ),
418        exp.SHA2: lambda self, e: self.func(
419            "ENCODE", sha256_sql(self, e), exp.Literal.string("hex")
420        ),
421        exp.SHA2Digest: sha2_digest_sql,
422        exp.StrPosition: lambda self, e: strposition_sql(self, e, func_name="POSITION"),
423        exp.StrToDate: lambda self, e: self.func("TO_DATE", e.this, self.format_time(e)),
424        exp.StrToTime: lambda self, e: self.func("TO_TIMESTAMP", e.this, self.format_time(e)),
425        exp.StructExtract: struct_extract_sql,
426        exp.Substring: _substring_sql,
427        exp.TimeFromParts: rename_func("MAKE_TIME"),
428        exp.TimestampFromParts: rename_func("MAKE_TIMESTAMP"),
429        exp.TimestampTrunc: timestamptrunc_sql(zone=True),
430        exp.TimestampAdd: _date_add_sql("+"),
431        exp.TimestampSub: _date_add_sql("-"),
432        exp.TimeStrToTime: timestrtotime_sql,
433        exp.TimeToStr: lambda self, e: self.func("TO_CHAR", e.this, self.format_time(e)),
434        exp.ToChar: lambda self, e: (
435            self.function_fallback_sql(e) if e.args.get("format") else self.tochar_sql(e)
436        ),
437        exp.Trim: trim_sql,
438        exp.TryCast: no_trycast_sql,
439        exp.TsOrDsAdd: _date_add_sql("+"),
440        exp.TsOrDsDiff: _date_diff_sql,
441        exp.UnixToTime: lambda self, e: self.func("TO_TIMESTAMP", e.this),
442        exp.Uuid: lambda *_: "GEN_RANDOM_UUID()",
443        exp.TimeToUnix: lambda self, e: self.func("DATE_PART", exp.Literal.string("epoch"), e.this),
444        exp.VariancePop: rename_func("VAR_POP"),
445        exp.Variance: rename_func("VAR_SAMP"),
446        exp.Xor: bool_xor_sql,
447        exp.Year: _day_month_year_sql,
448        exp.Unicode: rename_func("ASCII"),
449        exp.UnixToTime: _unix_to_time_sql,
450        exp.Levenshtein: _levenshtein_sql,
451        exp.JSONObjectAgg: rename_func("JSON_OBJECT_AGG"),
452        exp.JSONBObjectAgg: rename_func("JSONB_OBJECT_AGG"),
453        exp.CountIf: count_if_to_sum,
454        exp.FileFormatProperty: lambda self, e: f"USING {self.sql(e, 'this')}",
455        exp.TableSpaceProperty: lambda self, e: f"TABLESPACE {self.sql(e, 'this')}",
456    }
457
458    PROPERTIES_LOCATION = {
459        **generator.Generator.PROPERTIES_LOCATION,
460        exp.FileFormatProperty: exp.Properties.Location.POST_SCHEMA,
461        exp.OnCommitProperty: exp.Properties.Location.POST_WITH,
462        exp.PartitionedByProperty: exp.Properties.Location.POST_SCHEMA,
463        exp.TableSpaceProperty: exp.Properties.Location.POST_WITH,
464        exp.TransientProperty: exp.Properties.Location.UNSUPPORTED,
465        exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
466    }
467
468    def with_properties(self, properties: exp.Properties) -> str:
469        with_properties = []
470        post_with = []
471        for prop in properties.expressions:
472            if isinstance(prop, (exp.OnCommitProperty, exp.TableSpaceProperty)):
473                post_with.append(prop)
474            else:
475                with_properties.append(prop)
476
477        post_with.sort(key=lambda prop: isinstance(prop, exp.TableSpaceProperty))
478        sql = super().with_properties(exp.Properties(expressions=with_properties))
479        post_sql = self.expressions(sqls=post_with, sep=" ", indent=False)
480        return sql + self.seg(post_sql, sep=" " if sql else "") if post_sql else sql
481
482    def bytestring_sql(self, expression: exp.ByteString) -> str:
483        if not expression.args.get("is_bytes"):
484            return super().bytestring_sql(expression)
485
486        return self.func(
487            "DECODE",
488            exp.Literal.string(expression.this.encode("latin-1").hex()),
489            exp.Literal.string("hex"),
490        )
491
492    def schemacommentproperty_sql(self, expression: exp.SchemaCommentProperty) -> str:
493        self.unsupported("Table comments are not supported in the CREATE statement")
494        return ""
495
496    def commentcolumnconstraint_sql(self, expression: exp.CommentColumnConstraint) -> str:
497        self.unsupported("Column comments are not supported in the CREATE statement")
498        return ""
499
500    def columndef_sql(self, expression: exp.ColumnDef, sep: str = " ") -> str:
501        # PostgreSQL places parameter modes BEFORE parameter name
502        param_constraint = expression.find(exp.InOutColumnConstraint)
503
504        if param_constraint:
505            mode_sql = self.sql(param_constraint)
506            param_constraint.pop()  # Remove to prevent double-rendering
507            base_sql = super().columndef_sql(expression, sep)
508            return f"{mode_sql} {base_sql}"
509
510        return super().columndef_sql(expression, sep)
511
512    def unnest_sql(self, expression: exp.Unnest) -> str:
513        if len(expression.expressions) == 1:
514            arg = expression.expressions[0]
515            if isinstance(arg, exp.GenerateDateArray):
516                generate_series: exp.Expr = exp.GenerateSeries(**arg.args)
517                if isinstance(expression.parent, (exp.From, exp.Join)):
518                    generate_series = (
519                        exp.select("value::date")
520                        .from_(exp.Table(this=generate_series).as_("_t", table=["value"]))
521                        .subquery(expression.args.get("alias") or "_unnested_generate_series")
522                    )
523                return self.sql(generate_series)
524
525            from sqlglot.optimizer.annotate_types import annotate_types
526
527            this = annotate_types(arg, dialect=self.dialect)
528            if this.is_type("array<json>"):
529                while isinstance(this, exp.Cast):
530                    this = this.this
531
532                arg_as_json = self.sql(exp.cast(this, exp.DType.JSON))
533                alias = self.sql(expression, "alias")
534                alias = f" AS {alias}" if alias else ""
535
536                if expression.args.get("offset"):
537                    self.unsupported("Unsupported JSON_ARRAY_ELEMENTS with offset")
538
539                return f"JSON_ARRAY_ELEMENTS({arg_as_json}){alias}"
540
541        return super().unnest_sql(expression)
542
543    def bracket_sql(self, expression: exp.Bracket) -> str:
544        """Forms like ARRAY[1, 2, 3][3] aren't allowed; we need to wrap the ARRAY."""
545        if isinstance(expression.this, exp.Array):
546            expression.set("this", exp.paren(expression.this, copy=False))
547
548        return super().bracket_sql(expression)
549
550    def matchagainst_sql(self, expression: exp.MatchAgainst) -> str:
551        this = self.sql(expression, "this")
552        expressions = [f"{self.sql(e)} @@ {this}" for e in expression.expressions]
553        sql = " OR ".join(expressions)
554        return f"({sql})" if len(expressions) > 1 else sql
555
556    def alterset_sql(self, expression: exp.AlterSet) -> str:
557        exprs = self.expressions(expression, flat=True)
558        exprs = f"({exprs})" if exprs else ""
559
560        access_method = self.sql(expression, "access_method")
561        access_method = f"ACCESS METHOD {access_method}" if access_method else ""
562        tablespace = self.sql(expression, "tablespace")
563        tablespace = f"TABLESPACE {tablespace}" if tablespace else ""
564        option = self.sql(expression, "option")
565
566        return f"SET {exprs}{access_method}{tablespace}{option}"
567
568    def datatype_sql(self, expression: exp.DataType) -> str:
569        if expression.is_type(exp.DType.ARRAY):
570            if expression.expressions:
571                values = self.expressions(expression, key="values", flat=True)
572                return f"{self.expressions(expression, flat=True)}[{values}]"
573            return "ARRAY"
574
575        if expression.is_type(exp.DType.ENUM):
576            return f"ENUM ({self.expressions(expression, flat=True)})"
577
578        if expression.is_type(exp.DType.DOUBLE, exp.DType.FLOAT) and expression.expressions:
579            # Postgres doesn't support precision for REAL and DOUBLE PRECISION types
580            return f"FLOAT({self.expressions(expression, flat=True)})"
581
582        return super().datatype_sql(expression)
583
584    def cast_sql(self, expression: exp.Cast, safe_prefix: str | None = None) -> str:
585        this = expression.this
586
587        # Postgres casts DIV() to decimal for transpilation but when roundtripping it's superfluous
588        if (
589            isinstance(this, exp.IntDiv)
590            and exp.IntDiv in self.TRANSFORMS
591            and expression.to == exp.DType.DECIMAL.into_expr()
592        ):
593            return self.sql(this)
594
595        return super().cast_sql(expression, safe_prefix=safe_prefix)
596
597    def array_sql(self, expression: exp.Array) -> str:
598        exprs = expression.expressions
599        func_name = self.normalize_func("ARRAY")
600
601        if isinstance(seq_get(exprs, 0), exp.Query):
602            return f"{func_name}({self.sql(exprs[0])})"
603
604        return f"{func_name}{inline_array_sql(self, expression)}"
605
606    def computedcolumnconstraint_sql(self, expression: exp.ComputedColumnConstraint) -> str:
607        return f"GENERATED ALWAYS AS ({self.sql(expression, 'this')}) STORED"
608
609    def isascii_sql(self, expression: exp.IsAscii) -> str:
610        return f"({self.sql(expression.this)} ~ '^[[:ascii:]]*$')"
611
612    def ignorenulls_sql(self, expression: exp.IgnoreNulls) -> str:
613        # https://www.postgresql.org/docs/current/functions-window.html
614        self.unsupported("PostgreSQL does not support IGNORE NULLS.")
615        return self.sql(expression.this)
616
617    def respectnulls_sql(self, expression: exp.RespectNulls) -> str:
618        # https://www.postgresql.org/docs/current/functions-window.html
619        self.unsupported("PostgreSQL does not support RESPECT NULLS.")
620        return self.sql(expression.this)
621
622    def regexplike_sql(self, expression: exp.RegexpLike) -> str:
623        flag = expression.args.get("flag")
624        if not flag:
625            return self.binary(expression, "~")
626
627        # Postgres has no flags argument for ~, only the case-insensitive ~* operator. Of the
628        # case flags, the last one wins, e.g. 'ic' is case-sensitive and 'ci' is not
629        flags = flag.name if flag.is_string else ""
630        if not flag.is_string or set(flags) - {"i", "c"}:
631            self.unsupported("Only the case flags 'i' and 'c' are supported for ~ in Postgres")
632
633        return self.binary(expression, "~*" if flags.rfind("i") > flags.rfind("c") else "~")
634
635    @unsupported_args("this")
636    def currentschema_sql(self, expression: exp.CurrentSchema) -> str:
637        return "CURRENT_SCHEMA"
638
639    def interval_sql(self, expression: exp.Interval) -> str:
640        unit = expression.text("unit").lower()
641
642        this = expression.this
643        if unit.startswith("quarter") and isinstance(this, exp.Literal):
644            # Postgres has no QUARTER unit and no exponents in interval strings
645            quarters = to_int(this.to_py())
646            if quarters is None:
647                self.unsupported("Postgres intervals need a whole number of quarters")
648            else:
649                this.replace(exp.Literal.string(quarters * 3))
650                expression.args["unit"].replace(exp.var("MONTH"))
651
652        return super().interval_sql(expression)
653
654    def placeholder_sql(self, expression: exp.Placeholder) -> str:
655        if expression.args.get("jdbc"):
656            return "?"
657
658        this = f"({expression.name})" if expression.this else ""
659        return f"{self.NAMED_PLACEHOLDER_TOKEN}{this}s"
660
661    def arraycontains_sql(self, expression: exp.ArrayContains) -> str:
662        # Convert DuckDB's LIST_CONTAINS(array, value) to PostgreSQL
663        # DuckDB behavior:
664        #   - LIST_CONTAINS([1,2,3], 2) -> true
665        #   - LIST_CONTAINS([1,2,3], 4) -> false
666        #   - LIST_CONTAINS([1,2,NULL], 4) -> false (not NULL)
667        #   - LIST_CONTAINS([1,2,3], NULL) -> NULL
668        #
669        # PostgreSQL equivalent: CASE WHEN value IS NULL THEN NULL
670        #                            ELSE COALESCE(value = ANY(array), FALSE) END
671        value = expression.expression
672        array = expression.this
673
674        coalesce_expr = exp.Coalesce(
675            this=value.eq(exp.Any(this=exp.paren(expression=array, copy=False))),
676            expressions=[exp.false()],
677        )
678
679        case_expr = (
680            exp.Case()
681            .when(exp.Is(this=value, expression=exp.null()), exp.null(), copy=False)
682            .else_(coalesce_expr, copy=False)
683        )
684
685        return self.sql(case_expr)