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.
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.
SELECT * FROM spatial_ref_sys WHERE srtext LIKE '%\ /%'; Sometimes these ticks are very useful for searching special characters in a database.
<> is the standard SQL operator meaning "not equal". Many databases, including postgresql, supports != as a synonym for <> . They're exactly the same in postgresql.
"Is there a summarized guide?", well... The answer is no, so: hands-on! This answer is a Wiki, let's write.
Let,
ROUND
)ANY
)current_date
)The rules, using these elements, are in the form
When Op, Op1, Op2 are mathematical operators (ex. +
, -
. *
), and F() is a mathematical function (ex. ROUND()
).
Rules for scalar expressions and "pure array expressions":
Rules for operators as ALL
, ANY
, ROW
, SOME
, etc.
...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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With