Postgres Pro
Downloads
Home   >   mailing lists

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

From David G. Johnston
Subject Re: Where's the doc for "array()" — as in "select array(values (17), (42))"
Date
Msg-id CAKFQuwZPAfoT6+ksGtTD2q6EBFrqUHJOG+2yM8xUJGi62NUYeQ@mail.gmail.com
Whole thread Raw
In response to Re: Where's the doc for "array()" — as in "select array(values (17), (42))"  (Bryn Llewellyn <bryn@yugabyte.com>)
List pgsql-general
Tree view
On Mon, Sep 19, 2022 at 9:44 AM Bryn Llewellyn <bryn@yugabyte.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.

pgsql-general by date:

Previous
From: Bryn Llewellyn
Date:
Subject: Re: Where's the doc for "array()" — as in "select array(values (17), (42))"
Next
From: Tom Lane
Date:
Subject: Re: Re: Where's the doc for "array()" — as in "select array(values (17), (42))"