Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

The parentheses rules of PostgreSQL, is there a summarized guide?

In Mathematics and many programming languages (and I think standard SQL as well), parentheses change precedence (grouping parts to be evaluated first) or to enhance readability (for human eyes).
Equivalent Examples:

SELECT array[1,2] @> array[1]
SELECT (array[1,2]) @> array[1]

SELECT array[1,2] @> (array[1])
SELECT ((array[1,2]) @> (array[1]))

But SELECT 1 = ANY array[1,2] is a syntax error (!), and SELECT 1 = ANY (array[1,2]) is valid. Why?

OK, because "the manual says so". But what the logic for humans to remember all exceptions?
Is there a guide about it?

I do not understand why (expression) is the same as expression in some cases, but not in other cases.


PS1: parentheses are also used as value-list delimiters, as in expression IN (value [, ...]). But an array is not a value-list, and there does not seem to be a general rule in PostgreSQL when (array expression) is not the same as array expression.
Also, I used array as example, but this problem/question is not only about arrays.

like image 579
Peter Krauss Avatar asked Mar 31 '14 14:03

Peter Krauss


People also ask

What is $$ in PostgreSQL?

In PostgreSQL, the dollar-quoted string constants ($$) is used in user-defined functions and stored procedures. In PostgreSQL, you use single quotes for a string constant like this: select 'String constant'; When a string constant contains a single quote ('), you need to escape it by doubling up the single quote.

How do I find special characters in PostgreSQL?

SELECT * FROM spatial_ref_sys WHERE srtext LIKE '%\ /%'; Sometimes these ticks are very useful for searching special characters in a database.

What does <> mean in PostgreSQL?

<> is the standard SQL operator meaning "not equal". Many databases, including postgresql, supports != as a synonym for <> . They're exactly the same in postgresql.


2 Answers

"Is there a summarized guide?", well... The answer is no, so: hands-on! This answer is a Wiki, let's write.

Summarized guide

Let,

  • F() a an usual function. (ex. ROUND)
  • L() a function-like operator (ex. ANY)
  • f a operator-like function (ex. current_date)
  • Op an operator
  • Op1, Op2 are distinct operators
  • A, B, C values or expressions
  • S a expression-list, as "(A,B,C)"

The rules, using these elements, are in the form

  • rule: notes.

"pure" mathematical expressions

When Op, Op1, Op2 are mathematical operators (ex. +, -. *), and F() is a mathematical function (ex. ROUND()).

Rules for scalar expressions and "pure array expressions":

  • A Op B = (A Op B): the parentheses is optional.
  • A Op1 B Op2 C: need to check precedence.
  • (A Op1 B) Op2 C: enforce "first (A Op1 B)".
  • A Op1 (B Op2 C): enforce "first (B Op2 C)".
  • F(A) = (F(A)) = F((A)) = (F((A))): the parentheses are optional.
  • S = (S): the external parentheses are optional.
  • f=(f): the parentheses are optional.

Expressions with function-like operators

Rules for operators as ALL, ANY, ROW, SOME, etc.

  • L(A) = L((A)): the parentheses is optional in the argument.
  • (L(A)): SYNTAX ERROR.

...More rules? Please help editing here.

like image 86
3 revs Avatar answered Sep 23 '22 13:09

3 revs


ANY is a function-like construct. Like (almost) any other function in Postgres it requires parentheses around its parameters. Makes the syntax consistent and helps the parser avoid ambiguities.

You can think of ANY() like a shorthand for unnest() condensed to a single expression.

One might argue an additional set of parentheses around the set-variant of ANY. But that would be ambiguous, since a list of values in parentheses is interpreted as a single ROW type.

like image 21
Erwin Brandstetter Avatar answered Sep 26 '22 13:09

Erwin Brandstetter