CODEWITHSUNDEEP
postgresql
Sergey Onishchenko
Sergey Onishchenko

Reputation: 7881

Postgres wraps subquery results in parentheses

Don't pay attention to uselessness of the provided query, it's just a simplified part of a complex one.

I run query:

SELECT elem FROM (SELECT id FROM data) AS elem;

It yields the result: 

 elem 
------
 (5)
 (4)
 (24)
 (3)
 (23)

Why does each value enclosed in parentheses?

So, to fix it I do:

SELECT trim(elem::text, ')(') FROM (SELECT id FROM data) AS elem;

I have a feeling it should not be that way...

Upvotes: 10

Views: 4019

Answers (3)

Sergey Onishchenko
Sergey Onishchenko

Reputation: 7881

SELECT elem.id FROM (SELECT id FROM data) AS elem;

Upvotes: 5

ericj
ericj

Reputation: 2301

You select rows. Select columns instead:

SELECT elem FROM (SELECT id FROM data) AS tbl(elem);

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51649

https://www.postgresql.org/docs/9.6/static/rowtypes.html#ROWTYPES-USAGE

In PostgreSQL, a reference to a table name (or alias) in a query is effectively a reference to the composite value of the table's current row

and futher querying examples, also further regarding brackets:

The external text representation of a composite value consists of items that are interpreted according to the I/O conversion rules for the individual field types, plus decoration that indicates the composite structure. The decoration consists of parentheses (( and )) around the whole value, plus commas (,) between adjacent items.

also some relevant here: https://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS

Upvotes: 2

Related Questions