sqlglot.parsers.postgres

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, parser
  6from sqlglot.dialects.dialect import (
  7    DialectType,
  8    binary_from_function,
  9    build_formatted_time,
 10    build_json_extract_path,
 11    build_timestamp_trunc,
 12)
 13from sqlglot.helper import is_int, seq_get
 14from sqlglot.parser import IsPredicatePrecedence, binary_range_parser
 15from sqlglot.tokens import TokenType
 16
 17if t.TYPE_CHECKING:
 18    from sqlglot.dialects.dialect import Dialect
 19
 20
 21MAKE_INTERVAL_KWARGS = {
 22    "years": "year",
 23    "months": "month",
 24    "weeks": "week",
 25    "days": "day",
 26    "hours": "hour",
 27    "mins": "minute",
 28    "secs": "second",
 29}
 30
 31
 32def _build_make_interval(args: list) -> exp.MakeInterval:
 33    return exp.MakeInterval(
 34        **{
 35            MAKE_INTERVAL_KWARGS.get(value.this.name.lower(), key)
 36            if isinstance(value, exp.Kwarg)
 37            else key: value
 38            for key, value in zip(exp.MakeInterval.arg_types, args)
 39        }
 40    )
 41
 42
 43def _build_generate_series(args: list) -> exp.ExplodingGenerateSeries:
 44    # The goal is to convert step values like '1 day' or INTERVAL '1 day' into INTERVAL '1' day
 45    # Note: postgres allows calls with just two arguments -- the "step" argument defaults to 1
 46    step = seq_get(args, 2)
 47    if step is not None:
 48        if step.is_string:
 49            args[2] = exp.to_interval(step.this)
 50        elif isinstance(step, exp.Interval) and not step.args.get("unit"):
 51            args[2] = exp.to_interval(step.this.this)
 52
 53    return exp.ExplodingGenerateSeries.from_arg_list(args)
 54
 55
 56def _build_to_timestamp(args: list, dialect: Dialect) -> exp.UnixToTime | exp.StrToTime:
 57    # TO_TIMESTAMP accepts either a single double argument or (text, text)
 58    if len(args) == 1:
 59        # https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TABLE
 60        return exp.UnixToTime.from_arg_list(args)
 61
 62    # https://www.postgresql.org/docs/current/functions-formatting.html
 63    return build_formatted_time(exp.StrToTime)(args, dialect)
 64
 65
 66def _build_regexp_replace(args: list, dialect: DialectType = None) -> exp.RegexpReplace:
 67    # The signature of REGEXP_REPLACE is:
 68    # regexp_replace(source, pattern, replacement [, start [, N ]] [, flags ])
 69    #
 70    # Any one of `start`, `N` and `flags` can be column references, meaning that
 71    # unless we can statically see that the last argument is a non-integer string
 72    # (eg. not '0'), then it's not possible to construct the correct AST
 73    regexp_replace = None
 74    if len(args) > 3:
 75        last = args[-1]
 76        if not is_int(last.name):
 77            if not last.type or last.is_type(exp.DType.UNKNOWN, exp.DType.NULL):
 78                from sqlglot.optimizer.annotate_types import annotate_types
 79
 80                last = annotate_types(last, dialect=dialect)
 81
 82            if last.is_type(*exp.DataType.TEXT_TYPES):
 83                regexp_replace = exp.RegexpReplace.from_arg_list(args[:-1])
 84                regexp_replace.set("modifiers", last)
 85
 86    regexp_replace = regexp_replace or exp.RegexpReplace.from_arg_list(args)
 87    regexp_replace.set("single_replace", True)
 88    return regexp_replace
 89
 90
 91def _build_levenshtein_less_equal(args: list) -> exp.Levenshtein:
 92    # Postgres has two signatures for levenshtein_less_equal function, but in both cases
 93    # max_dist is the last argument
 94    # levenshtein_less_equal(source, target, ins_cost, del_cost, sub_cost, max_d)
 95    # levenshtein_less_equal(source, target, max_d)
 96    max_dist = args.pop()
 97
 98    return exp.Levenshtein(
 99        this=seq_get(args, 0),
100        expression=seq_get(args, 1),
101        ins_cost=seq_get(args, 2),
102        del_cost=seq_get(args, 3),
103        sub_cost=seq_get(args, 4),
104        max_dist=max_dist,
105    )
106
107
108class PostgresParser(parser.Parser):
109    SUPPORTS_TABLE_QUERY = True
110
111    IS_PREDICATE_PRECEDENCE = IsPredicatePrecedence.LOW
112
113    ALTER_DROP_REQUIRES_COLUMN = False
114    ALTER_TABLE_MIXED_ACTIONS = True
115    ALTER_COLUMN_TYPE_REQUIRES_KEYWORD = True
116    SUPPORTS_OMITTED_INTERVAL_SPAN_UNIT = True
117
118    # The one-byte "char" type is distinct from CHAR, and it can only be referenced by
119    # quoting it: https://www.postgresql.org/docs/current/datatype-character.html
120    QUOTED_TYPES_TO_PRESERVE: t.ClassVar = {"char"}
121
122    PROPERTY_PARSERS = {
123        **{k: v for k, v in parser.Parser.PROPERTY_PARSERS.items() if k != "INPUT"},
124        "SET": lambda self: self.expression(exp.SetConfigProperty(this=self._parse_set())),
125        "TABLESPACE": lambda self: self.expression(
126            exp.TableSpaceProperty(this=self._parse_id_var())
127        ),
128    }
129
130    PLACEHOLDER_PARSERS = {
131        **parser.Parser.PLACEHOLDER_PARSERS,
132        TokenType.PLACEHOLDER: lambda self: self.expression(exp.Placeholder(jdbc=True)),
133        TokenType.MOD: lambda self: self._parse_query_parameter(),
134    }
135
136    FUNCTIONS = {
137        **parser.Parser.FUNCTIONS,
138        "ARRAY_PREPEND": lambda args: exp.ArrayPrepend(
139            this=seq_get(args, 1), expression=seq_get(args, 0)
140        ),
141        "BIT_AND": exp.BitwiseAndAgg.from_arg_list,
142        "BIT_OR": exp.BitwiseOrAgg.from_arg_list,
143        "BIT_XOR": exp.BitwiseXorAgg.from_arg_list,
144        "BTRIM": exp.Trim.from_arg_list,
145        "VERSION": exp.CurrentVersion.from_arg_list,
146        "DATE_TRUNC": build_timestamp_trunc,
147        "DIV": lambda args: exp.cast(binary_from_function(exp.IntDiv)(args), exp.DType.DECIMAL),
148        "GENERATE_SERIES": _build_generate_series,
149        "GET_BIT": lambda args: exp.Getbit(
150            this=seq_get(args, 0), expression=seq_get(args, 1), zero_is_msb=True
151        ),
152        "JSON_EXTRACT_PATH": build_json_extract_path(exp.JSONExtract),
153        "JSON_EXTRACT_PATH_TEXT": build_json_extract_path(exp.JSONExtractScalar),
154        "LENGTH": lambda args: exp.Length(this=seq_get(args, 0), encoding=seq_get(args, 1)),
155        "MAKE_INTERVAL": _build_make_interval,
156        "MAKE_TIME": exp.TimeFromParts.from_arg_list,
157        "MAKE_TIMESTAMP": exp.TimestampFromParts.from_arg_list,
158        "NOW": exp.CurrentTimestamp.from_arg_list,
159        "REGEXP_REPLACE": _build_regexp_replace,
160        "TO_CHAR": build_formatted_time(exp.TimeToStr),
161        "TO_DATE": build_formatted_time(exp.StrToDate),
162        "TO_TIMESTAMP": _build_to_timestamp,
163        "UNNEST": exp.Explode.from_arg_list,
164        "SHA256": lambda args: exp.SHA2Digest(
165            this=seq_get(args, 0), length=exp.Literal.number(256)
166        ),
167        "SHA384": lambda args: exp.SHA2Digest(
168            this=seq_get(args, 0), length=exp.Literal.number(384)
169        ),
170        "SHA512": lambda args: exp.SHA2Digest(
171            this=seq_get(args, 0), length=exp.Literal.number(512)
172        ),
173        "LEVENSHTEIN_LESS_EQUAL": _build_levenshtein_less_equal,
174        "JSON_OBJECT_AGG": lambda args: exp.JSONObjectAgg(expressions=args),
175        "JSONB_OBJECT_AGG": exp.JSONBObjectAgg.from_arg_list,
176        "WIDTH_BUCKET": lambda args: (
177            exp.WidthBucket(this=seq_get(args, 0), threshold=seq_get(args, 1))
178            if len(args) == 2
179            else exp.WidthBucket.from_arg_list(args)
180        ),
181        "UUID": lambda args: exp.cast(args[0], exp.DType.UUID) if args else exp.Uuid(),
182    }
183
184    NO_PAREN_FUNCTION_PARSERS = {
185        **parser.Parser.NO_PAREN_FUNCTION_PARSERS,
186        "VARIADIC": lambda self: self.expression(exp.Variadic(this=self._parse_bitwise())),
187    }
188
189    NO_PAREN_FUNCTIONS = {
190        **parser.Parser.NO_PAREN_FUNCTIONS,
191        TokenType.LOCALTIME: exp.Localtime,
192        TokenType.LOCALTIMESTAMP: exp.Localtimestamp,
193        TokenType.CURRENT_CATALOG: exp.CurrentCatalog,
194        TokenType.SESSION_USER: exp.SessionUser,
195        TokenType.CURRENT_SCHEMA: exp.CurrentSchema,
196    }
197
198    FUNCTION_PARSERS = {
199        **parser.Parser.FUNCTION_PARSERS,
200        "DATE_PART": lambda self: self._parse_date_part(),
201        "JSON_AGG": lambda self: self.expression(
202            exp.JSONArrayAgg(this=self._parse_lambda(), order=self._parse_order())
203        ),
204        "JSONB_EXISTS": lambda self: self._parse_jsonb_exists(),
205        "JSON_VALUE": lambda self: self._parse_json_value(json_path=False),
206    }
207
208    BITWISE = {
209        **parser.Parser.BITWISE,
210        TokenType.HASH: exp.BitwiseXor,
211    }
212
213    EXPONENT = {
214        TokenType.CARET: exp.Pow,
215    }
216
217    RANGE_PARSERS = {
218        **parser.Parser.RANGE_PARSERS,
219        TokenType.DAMP: binary_range_parser(exp.ArrayOverlaps),
220        TokenType.DAT: lambda self, this: self.expression(
221            exp.MatchAgainst(this=self._parse_bitwise(), expressions=[this])
222        ),
223    }
224
225    STATEMENT_PARSERS = {
226        **parser.Parser.STATEMENT_PARSERS,
227        TokenType.END: lambda self: self._parse_commit_or_rollback(),
228    }
229
230    UNARY_PARSERS = {
231        **parser.Parser.UNARY_PARSERS,
232        # The `~` token is remapped from TILDE to RLIKE in Postgres due to the binary REGEXP LIKE operator
233        TokenType.RLIKE: lambda self: self.expression(exp.BitwiseNot(this=self._parse_unary())),
234    }
235
236    JSON_ARROWS_REQUIRE_JSON_TYPE = True
237
238    COLUMN_OPERATORS = {
239        k: v
240        for k, v in parser.Parser.COLUMN_OPERATORS.items()
241        if k
242        not in (
243            TokenType.ARROW,
244            TokenType.DARROW,
245            TokenType.HASH_ARROW,
246            TokenType.DHASH_ARROW,
247            TokenType.PLACEHOLDER,
248        )
249    }
250
251    JSON_OPERATORS = {
252        TokenType.ARROW: lambda self, this, path: self.validate_expression(
253            build_json_extract_path(
254                exp.JSONExtract, arrow_req_json_type=self.JSON_ARROWS_REQUIRE_JSON_TYPE
255            )([this, path])
256        ),
257        TokenType.DARROW: lambda self, this, path: self.validate_expression(
258            build_json_extract_path(
259                exp.JSONExtractScalar, arrow_req_json_type=self.JSON_ARROWS_REQUIRE_JSON_TYPE
260            )([this, path])
261        ),
262        TokenType.HASH_ARROW: parser.build_jsonb_extract,
263        TokenType.DHASH_ARROW: parser.build_jsonb_extract_scalar,
264        TokenType.PLACEHOLDER: parser.build_jsonb_contains_top_key,
265    }
266
267    ARG_MODE_TOKENS: t.ClassVar = {TokenType.IN, TokenType.OUT, TokenType.INOUT, TokenType.VARIADIC}
268
269    def _parse_parameter_mode(self) -> TokenType | None:
270        """
271        Parse PostgreSQL function parameter mode (IN, OUT, INOUT, VARIADIC).
272
273        Disambiguates between mode keywords and identifiers with the same name:
274        - MODE TYPE      -> keyword is identifier (e.g., "out INT")
275        - MODE NAME TYPE -> keyword is mode (e.g., "OUT x INT")
276
277        Returns:
278            Mode token type if current token is a mode keyword, None otherwise.
279        """
280        if not self._match_set(self.ARG_MODE_TOKENS, advance=False) or not self._next:
281            return None
282
283        mode_token = self._curr
284
285        # Check Pattern 1: MODE TYPE
286        # Try parsing next token as a built-in type (not UDT)
287        # If successful, the keyword is an identifier, not a mode
288        is_followed_by_builtin_type = self._try_parse(
289            lambda: (
290                self._advance()  # type: ignore
291                or self._parse_types(check_func=False, allow_identifiers=False)
292            ),
293            retreat=True,
294        )
295        if is_followed_by_builtin_type:
296            return None  # Pattern: "out INT" -> out is parameter name
297
298        # Check Pattern 2: MODE NAME TYPE
299        # If next token is an identifier, check if there's a type after it
300        # The type can be built-in or user-defined (allow_identifiers=True)
301        if self._next.token_type not in self.ID_VAR_TOKENS:
302            return None
303
304        is_followed_by_any_type = self._try_parse(
305            lambda: (
306                self._advance(2)  # type: ignore
307                or self._parse_types(check_func=False, allow_identifiers=True)
308            ),
309            retreat=True,
310        )
311
312        if is_followed_by_any_type:
313            return mode_token.token_type  # Pattern: "OUT x INT" -> OUT is mode
314
315        return None
316
317    def _create_mode_constraint(self, param_mode: TokenType) -> exp.InOutColumnConstraint:
318        """
319        Create parameter mode constraint for function parameters.
320
321        Args:
322            param_mode: The parameter mode token (IN, OUT, INOUT, or VARIADIC).
323
324        Returns:
325            InOutColumnConstraint expression representing the parameter mode.
326        """
327        return self.expression(
328            exp.InOutColumnConstraint(
329                input_=(param_mode in {TokenType.IN, TokenType.INOUT}),
330                output=(param_mode in {TokenType.OUT, TokenType.INOUT}),
331                variadic=(param_mode == TokenType.VARIADIC),
332            )
333        )
334
335    def _parse_function_parameter(self) -> exp.Expr | None:
336        param_mode = self._parse_parameter_mode()
337
338        if param_mode:
339            self._advance()
340
341        # Parse parameter name and type
342        param_name = self._parse_id_var()
343        column_def = self._parse_column_def(this=param_name, computed_column=False)
344
345        # Attach mode as constraint
346        if param_mode and column_def:
347            constraint = self._create_mode_constraint(param_mode)
348            if not column_def.args.get("constraints"):
349                column_def.set("constraints", [])
350            column_def.args["constraints"].insert(0, constraint)
351
352        return column_def
353
354    def _parse_query_parameter(self) -> exp.Expr | None:
355        this = (
356            self._parse_wrapped(self._parse_id_var)
357            if self._match(TokenType.L_PAREN, advance=False)
358            else None
359        )
360        self._match_text_seq("S")
361        return self.expression(exp.Placeholder(this=this))
362
363    def _parse_date_part(self) -> exp.Expr:
364        part = self._parse_type()
365        self._match(TokenType.COMMA)
366        value = self._parse_bitwise()
367
368        if part and isinstance(part, (exp.Column, exp.Literal)):
369            part = exp.var(part.name.upper())
370
371        return self.expression(exp.Extract(this=part, expression=value))
372
373    def _parse_unique(self) -> exp.UniqueColumnConstraint:
374        return self.expression(
375            exp.UniqueColumnConstraint(
376                nulls=self._match_text_seq("NULLS", "NOT", "DISTINCT"),
377                this=self._parse_schema(),
378                include=self._parse_index_params(),
379                options=self._parse_key_constraint_options(),
380            )
381        )
382
383    def _parse_primary_key(
384        self,
385        wrapped_optional: bool = False,
386        in_props: bool = False,
387        named_primary_key: bool = False,
388    ) -> exp.PrimaryKeyColumnConstraint | exp.PrimaryKey:
389        this = super()._parse_primary_key(wrapped_optional, in_props, named_primary_key)
390        if isinstance(this, exp.PrimaryKeyColumnConstraint):
391            this.set("include", self._parse_index_params())
392            this.set("options", this.args["options"] + self._parse_key_constraint_options())
393        return this
394
395    def _parse_jsonb_exists(self) -> exp.JSONBExists:
396        return self.expression(
397            exp.JSONBExists(
398                this=self._parse_bitwise(),
399                path=self._match(TokenType.COMMA)
400                and self.dialect.to_json_path(self._parse_bitwise()),
401            )
402        )
403
404    def _parse_generated_as_identity(
405        self,
406    ) -> (
407        exp.GeneratedAsIdentityColumnConstraint
408        | exp.ComputedColumnConstraint
409        | exp.GeneratedAsRowColumnConstraint
410    ):
411        this = super()._parse_generated_as_identity()
412
413        if self._match_text_seq("STORED"):
414            this = self.expression(exp.ComputedColumnConstraint(this=this.expression))
415
416        return this
417
418    def _parse_user_defined_type(self, identifier: exp.Identifier) -> exp.Expr | None:
419        udt_type: exp.Identifier | exp.Dot = identifier
420
421        while self._match(TokenType.DOT):
422            part = self._parse_id_var()
423            if part:
424                udt_type = exp.Dot(this=udt_type, expression=part)
425
426        return exp.DataType.build(udt_type, udt=True)