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

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Mon, Sep 19, 2022 at 9:44 AM Bryn Llewellyn <bryn@xxxxxxxxxxxx> 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.