5

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.

Community
  • 1
  • 1
Peter Krauss
  • 13,174
  • 24
  • 167
  • 304
  • 1
    Since this is SQL, count yourself lucky that you don't have to write it as a mock-English phrase, like `SELECT 1 HAS ANY VALUE FROM ARRAY WITH VALUES 1 AND 2 AND 3 END` ;) – IMSoP Mar 31 '14 at 15:23

2 Answers2

9

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

Community
  • 1
  • 1
Peter Krauss
  • 13,174
  • 24
  • 167
  • 304
3

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 4
    In the right context, parens can also be a [row constructor](http://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS). So `SELECT (expression-list)...` is equivalent to `SELECT ROW(expression-list)...`. – Mike Sherrill 'Cat Recall' Mar 31 '14 at 14:58
  • @MikeSherrill'CatRecall': Just thought of the same. :) Added the link you provided. – Erwin Brandstetter Mar 31 '14 at 14:59
  • Yes, thanks! "function-like construct" make sense! *ALL*, *ANY*, *ROW* and *SOME* are in the real life `ALL()`, `ANY()`, `ROW()` and `SOME()` as usual functions syntax... I must to register here my "personal indignation" with [the PostgreSQL guide](http://www.postgresql.org/docs/current/interactive/functions-comparisons.html) (!): no mention to "function" or "function-like", only *"... right-hand side is a parenthesized expression ... left-hand expression is evaluated and compared to each element"*, and no objective explanation. – Peter Krauss Mar 31 '14 at 15:19
  • Not to add to your indignation, but there is even a handful of functions *without* parentheses (adhering to the SQL standard and for historical reasons). Like [`current_date` or `current_timestamp`.](http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TABLE) – Erwin Brandstetter Mar 31 '14 at 16:19