Convert Subquery Result to Array

By mkyong | Updated: May 31, 2010

Viewed: 2,528 (+10 pv/w)

Tags: postgresql sql

I want my subquery results (multi rows) return as a singale row in my query. For example.

select u.url, (select c.categoryid from category c 
where c.categoryid = u.categoryid) from url u

If url contains multiple categories, subquery will return multiple rows as following
P.S Subquery return multiple rows is not supported in most of the database but PostgreSQL

url | categoryid
---------------------
1   |  1
1   |  2
2   |  1

Result i wanted is as following

url | categoryid
---------------------
1   |  1,2
2   |  1

I’m not sure how easy it can achieve in others database like oracle or mysql, however PostgreSQL provided Array data type, using array it’s very easy to get what i want. I have to changed my query to

select u.url, (select array(select c.categoryid from category c 
where c.categoryid = u.categoryid))as categoryid from url u

Result ~

url | categoryid
---------------------
1   |  {1,2}
2   |  1

Done ~ PostgreSQL Array Rock ~