PostgreSQL 8.4 - array_agg with order

Question

|id     |profile     |data
|1      |name1       |2.1
|1      |name2       |400
|1      |name3       |200
|2      |name1       |3.4
|2      |name2       |350
|2      |name3       |500

This is what I have at the moment. When I ran this query:

SELECT id, ARRAY_AGG(profile), ARRAY_AGG(data) FROM "schema"."table" GROUP BY id;

I got:

|id     |array(profile)           |array(data)
|1      |{name1, name2, name3}    |{2.1, 400, 200}
|2      |{name2, name3, name1}    |{350, 500, 3.4}

I also tried to pre-sort

SELECT id, ARRAY_AGG(profile), ARRAY_AGG(data) FROM (SELECT * FROM "schema"."table" ORDER BY id) A GROUP BY id;

The data position match on both array but the format is not consistent. I wanted this result:

|id     |array(profile)           |array(data)
|1      |{name1, name2, name3}    |{2.1, 400, 200}
|2      |{name1, name2, name3}    |{3.4, 350, 500}

I am using PostgreSQL 8.4 so I cannot use array_agg(profile ORDER BY data).

klin · Accepted Answer · 2017-11-02 18:20:32Z

3

As far as I remember Postgres 8.4, you can try to execute aggregates on a sorted derived table (unfortunately I cannot run 8.4 to verify this), e.g.:

select id, array_agg(profile), array_agg(data) 
from (
    select *
    from my_table
    order by id, profile) s
group by id
order by id;

answered Nov 2, 2017 at 18:20

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL 8.4 - array_agg with order

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related