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