Postgresql select rows(a result) as array

Question

Assume I have following table, plus some data.

create table "common"."log"("id" bigserial primary key, 
                            "level" int not null default 0);

Now I have this select query that would return something like this.
select * from common.log where id=147;

+------+--------+
|id    |level   |
+------+--------+
|147   |1       |
|147   |2       |
|147   |2       |
|147   |6       |
|147   |90      |
+------+--------+

Now I like to have something like following rather above

+------+---------------+
|id    |arr_level      |
+------+---------------+
|147   |{1,2,2,6,90}   |
+------+---------------+

So is there any implicit select clause/way for doing this? thanks.

pgsql v9.3

geoandri · Accepted Answer · 2014-10-31 10:01:22Z

1

You can user array function like this

 Select '147' as id,array(select level from common.log where id=147) as arr_level;

answered Oct 31, 2014 at 10:01

geoandri

2,4282 gold badges18 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

harmic · Accepted Answer · 2014-10-31 11:41:04Z

1

Another way, probably more useful if you have more than one id to query:

SELECT id, array_agg(level) FROM common.log GROUP BY id;

See: aggregate functions.

answered Oct 31, 2014 at 11:41

harmic

31k6 gold badges75 silver badges102 bronze badges

Collectives™ on Stack Overflow

Postgresql select rows(a result) as array

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related