Thread: need help with query, how to fold select result to array?
eps=# SELECT office_id, serial, commit_date, service_id, meter_id, organization_reading, reading FROM meter_readings WHERE office_id = 134 AND serial = 27 AND commit_date = '2010-01-11' AND commit_time = '13:44:37' AND person_id = 300871; office_id | serial | commit_date | service_id | meter_id | organization_reading | reading -----------+--------+-------------+------------+----------+----------------------+--------- 134 | 27 | 2010-01-11 | 2 | 71629130 | 15518 | 0 134 | 27 | 2010-01-11 | 2 | 2668722 | 616 | 0 134 | 27 | 2010-01-11 | 75 | 111029 | 9505 | 0 134 | 27 | 2010-01-11 | 4 | 019210 | 372 | 0 (4 rows) How to get ? 134 | 27 | 2010-01-11 | 2 | {{71629130, 15518, 0}, {2668722, 616, 0}} 134 | 27 | 2010-01-11 | 75 | {111029, 9505, 0} 134 | 27 | 2010-01-11 | 4 | {019210, 372, 0} Thanks a lot!
In response to Sergey Levchenko : > eps=# SELECT office_id, serial, commit_date, service_id, meter_id, > organization_reading, reading FROM meter_readings WHERE office_id = > 134 AND serial = 27 AND commit_date = '2010-01-11' AND commit_time = > '13:44:37' AND person_id = 300871; > > office_id | serial | commit_date | service_id | meter_id | > organization_reading | reading > -----------+--------+-------------+------------+----------+----------------------+--------- > 134 | 27 | 2010-01-11 | 2 | 71629130 | > 15518 | 0 > 134 | 27 | 2010-01-11 | 2 | 2668722 | > 616 | 0 > 134 | 27 | 2010-01-11 | 75 | 111029 | > 9505 | 0 > 134 | 27 | 2010-01-11 | 4 | 019210 | > 372 | 0 > (4 rows) > > How to get ? > 134 | 27 | 2010-01-11 | 2 | {{71629130, 15518, > 0}, {2668722, 616, 0}} > 134 | 27 | 2010-01-11 | 75 | {111029, 9505, 0} > 134 | 27 | 2010-01-11 | 4 | {019210, 372, 0} > > Thanks a lot! You can use array_agg(since 8.4): test=# create table bla (id int, value text); CREATE TABLE test=*# copy bla from stdin; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1 bla >> 1 foo >> 1 bar >> 2 foobar >> 2 test >> \. test=*# select id, array_agg(value) from bla group by 1 order by 1; id | array_agg ----+--------------- 1 | {bla,foo,bar} 2 | {foobar,test} (2 rows) If you don't have 8.4 search the docu for array_accum: http://www.postgresql.org/docs/8.4/interactive/xaggr.html Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
but I have to fold it in multi-dimensional, array like {{71629130, 15518, 0}, {2668722, 616, 0}} ? but it's not possible to pass more then one argument to the array_agg function :/ 2010/1/12 A. Kretschmer <andreas.kretschmer@schollglas.com>: > In response to Sergey Levchenko : >> eps=# SELECT office_id, serial, commit_date, service_id, meter_id, >> organization_reading, reading FROM meter_readings WHERE office_id = >> 134 AND serial = 27 AND commit_date = '2010-01-11' AND commit_time = >> '13:44:37' AND person_id = 300871; >> >> office_id | serial | commit_date | service_id | meter_id | >> organization_reading | reading >> -----------+--------+-------------+------------+----------+----------------------+--------- >> 134 | 27 | 2010-01-11 | 2 | 71629130 | >> 15518 | 0 >> 134 | 27 | 2010-01-11 | 2 | 2668722 | >> 616 | 0 >> 134 | 27 | 2010-01-11 | 75 | 111029 | >> 9505 | 0 >> 134 | 27 | 2010-01-11 | 4 | 019210 | >> 372 | 0 >> (4 rows) >> >> How to get ? >> 134 | 27 | 2010-01-11 | 2 | {{71629130, 15518, >> 0}, {2668722, 616, 0}} >> 134 | 27 | 2010-01-11 | 75 | {111029, 9505, 0} >> 134 | 27 | 2010-01-11 | 4 | {019210, 372, 0} >> >> Thanks a lot! > > You can use array_agg(since 8.4): > > test=# create table bla (id int, value text); > CREATE TABLE > test=*# copy bla from stdin; > Enter data to be copied followed by a newline. > End with a backslash and a period on a line by itself. >>> 1 bla >>> 1 foo >>> 1 bar >>> 2 foobar >>> 2 test >>> \. > test=*# select id, array_agg(value) from bla group by 1 order by 1; > id | array_agg > ----+--------------- > 1 | {bla,foo,bar} > 2 | {foobar,test} > (2 rows) > > > If you don't have 8.4 search the docu for array_accum: > http://www.postgresql.org/docs/8.4/interactive/xaggr.html > > > Regards, Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
CREATE TYPE varchararray AS (f varchar[]); SELECT office_id, serial, commit_date, service_id, array_agg(DISTINCT ROW(ARRAY(SELECT meter_id::varchar UNION ALL SELECT organization_reading::varchar UNION ALL SELECT reading::varchar))::varchararray) FROM meter_readings WHERE office_id = 134 AND serial = 27 AND commit_date = '2010-01-11' AND commit_time = '13:44:37' AND person_id = 300871 GROUP BY office_id, serial, commit_date, service_id; office_id | serial | commit_date | service_id | array_agg -----------+--------+-------------+------------+------------------------------------------------------ 134 | 27 | 2010-01-11 | 2 | {"(\"{2668722,616,0}\")","(\"{71629130,15518,0}\")"} 134 | 27 | 2010-01-11 | 4 | {"(\"{019210,372,0}\")"} 134 | 27 | 2010-01-11 | 75 | {"(\"{111029,9505,0}\")"} (3 rows) I want something like that, but without varchararray type; parentheses, slashes and " in array_agg field, where does it get from?
On Tue, Jan 12, 2010 at 04:06:20PM +0200, Sergey Levchenko wrote: > but I have to fold it in multi-dimensional, array like {{71629130, > 15518, 0}, {2668722, 616, 0}} ? > but it's not possible to pass more then one argument to the array_agg > function :/ Tuples work fine. Multidimensional arrays are awfully complicated and it's generally better to stay away from them, arrays of tuples make more sense. Maybe something like: CREATE TYPE meterreading ( meter_id INT, org_reading NUMERIC, reading NUMERIC ); SELECT office_id, serial, commit_date, service_id, array_agg(row(meter_id, organization_reading, reading)::meterreading) AS readings FROM meter_readings; -- Sam http://samason.me.uk/