Re: Where's the doc for "array()" — as in "select array(values (17), (42))"

On Mon, Sep 19, 2022 at 9:44 AM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

>
> There must be a significant difference between this:
>
> select 'dog'
>

That isn't anything in particular (it is a command by itself, it can be a
subquery in a FROM clause or the ARRAY() expression.

>
> and this:
>
> (select 'dog')
>

That is a scalar subquery the produces exactly one row and one column that
can be substituted in anyplace a single value is required.

> This works fine:
>
> select length( (select 'dog') )
>
> But without the doubled parentheses, it causes a syntax error.
>

Scalar subqueries are required to have surrounding parentheses.

https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES

"A scalar subquery is an ordinary SELECT query in parentheses that returns
exactly one row with one column."

> On the other hand, an extra pair of surrounding parentheses here
>
> select array( (values (17), (42)) )
>
> while not necessary, *is* tolerated.
>

An actual subquery works here so the parentheses are grouping in nature and
not an inherent part of the syntax.

David J.