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