array_agg and partition sorts

From: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: array_agg and partition sorts
Date: 2013-06-26 09:04:05
Message-ID: 20130626090405.GA917@erota.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm on Postgres 9.1 and I've come across an issue which shows I don't
understand partition sorting:

Given a table like this:

select * from test;
n_group | t_name | t_additional
---------+-------------+--------------
1 | Canberra | Australia
1 | Vienna | Austria
1 | Baku | Azerbaijan
1 | Nassau | The Bahamas
1 | Dhaka | Bangladesh
1 | Porto-Novo | Benin
1 | Thimphu | Bhutan
1 | Brasilia | Brazil
1 | Sofia | Bulgaria
1 | Ouagadougou | Burkina Faso
2 | Bridgetown | Barbados
2 | Minsk | Belarus
2 | Brussels | Belgium
2 | Belmopan | Belize
2 | Gaborone | Botswana

Running this query:

select * from (
select
array_agg(t_name) over (partition by n_group) as agg1
, array_agg(t_name) over (partition by n_group order by t_additional desc) as agg2
, array_agg(t_name) over (partition by n_group order by t_additional asc) as agg3
, row_number() over (partition by n_group)
from test
) x
where
x.row_number = 1;

Returns:

-[ RECORD 1 ]---------------------------------------------------------------------------------
agg1 | {Canberra,Vienna,Baku,Nassau,Dhaka,Porto-Novo,Thimphu,Brasilia,Sofia,Ouagadougou}
agg2 | {Nassau,Ouagadougou,Sofia,Brasilia,Thimphu,Porto-Novo,Dhaka,Baku,Vienna,Canberra}
agg3 | {Canberra}
row_number | 1
-[ RECORD 2 ]---------------------------------------------------------------------------------
agg1 | {Bridgetown,Minsk,Brussels,Belmopan,Gaborone}
agg2 | {Gaborone,Belmopan,Brussels,Minsk,Bridgetown}
agg3 | {Bridgetown}
row_number | 1

I don't understand why agg3 is missing values.

--
Rory Campbell-Lange
rory(at)campbell-lange(dot)net

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jiří Pavlovský 2013-06-26 09:11:05 Re: utf8 errors
Previous Message Jiří Pavlovský 2013-06-26 09:03:02 Re: utf8 errors