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