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.
"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
"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.
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.
