On Wed, Feb 24, 2010 at 02:09:09PM +0100, A. Kretschmer wrote: > In response to Louis-David Mitterrand : > > Hi, > > > > I'm trying the following query: > > > > select array_agg(t1.id) from table1 t1 join table2 t2 on (t2.id = > > any(array_agg)) group by t1.col1; > > > > but I get this error: ERROR: column "array_agg" does not exist > > > > I tried aliasing array_agg(t1.id) without success. > > > > Thanks for any suggestions,
> I can't really understand what you want to achieve, but maybe this is > what you are looking for: Here is a test case I built. I want to list all cruises by cruise_type but after merging cruise_type that have the same cruise_type_name: drop table cruise; drop table cruise_type; create table cruise_type ( id_cruise_type serial primary key, cruise_type_name text ); create table cruise ( id_cruise serial, id_cruise_type integer references cruise_type, cruise_date timestamp default now() ); insert into cruise_type (cruise_type_name) values ('5 day eastern carribean cruise'), ('5 day western carribean cruise'), ('5 day eastern carribean cruise'), ('5 day western carribean cruise') ; insert into cruise (id_cruise_type) values (1), (2), (3), (4), (1), (2), (3), (4) ; select array_agg(ct.id_cruise_type),ct.cruise_type_name from cruise_type ct join cruise c on (c.id_cruise = any(array_agg)) group by cruise_type_name; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql