Re: Use of array_agg and array string on inner query

On Thu, May 19, 2016 at 1:09 AM shankha <shankhabanerjee(at)gmail(dot)com> wrote:

> I have the following piece of code:
>
> DROP SCHEMA IF EXISTS s CASCADE;
> CREATE SCHEMA s;
>
> CREATE TABLE "s"."t1"
> (
> "c1" BigSerial PRIMARY KEY,
> "c2" BigInt NOT NULL,
> "c3" BigInt
> )
> WITH (OIDS=FALSE);
>
> INSERT INTO s.t1 (c2) VALUES (10);
> INSERT INTO s.t1 (c2, c3) VALUES (20, 10);
> INSERT INTO s.t1 (c2, c3) VALUES (30, 10);
>
> /* 1. */ SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2;
>
> /* 2. */ SELECT t1.c1, ARRAY_TO_STRING(ARRAY_AGG((t2.c1)), ',')
> FROM s.t1 LEFT JOIN s.t1 as t2
> ON t2.c3 = t1.c2 GROUP BY t1.c1;
>
> /* 3. */ SELECT c1, c2,
> ARRAY_TO_STRING(ARRAY_AGG((SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1
> as t2 ON t3.c3 = t2.c2)), ',')
> FROM s.t1 t1
> GROUP BY c1;
> DROP SCHEMA s CASCADE;
>

The query

SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2 produces
multiple rows. Since you are calling the aggregate function on the result
set and not as part of the expression, you are not able to get single row
as an output.

> The output for 1 query:
>
> c1
> ----
> 2
> 3
> (2 rows)
>
> 2 Query:
>
> c1 | array_to_string
> ----+-----------------
> 1 | 2,3
> 2 |
> 3 |
> (3 rows)
>
> 3 Query gives me a error:
>
> psql:/tmp/aggregate.sql:24: ERROR: more than one row returned
> by a subquery used as an expression
>
>
> The 3 query uses 1 query as inner query. Is there a way to make Query
> 3 work with inner query as 1 rather than reverting to 2.
>
> 3 output should be same as 2.
>
> I understand that the error message says query 1 when used as sub
> query of 3 cannot return more than one row.
>
> Pardon my limited knowledge of database.
>
>
> I have tried out:
>
> SELECT c1, c2,
> ARRAY_TO_STRING((SELECT ARRAY_AGG(t3.c1) FROM s.t1 as t3 JOIN s.t1
> as t2 ON t3.c3 = t2.c2), ',')
> FROM s.t1 t1
> GROUP BY c1;
>
>
This would work since the aggregate function has been used on the column.

> Output is :
>
> c1 | c2 | array_to_string
> ----+----+-----------------
> 2 | 20 | 2,3
> 1 | 10 | 2,3
> 3 | 30 | 2,3
>
> Could one of you help me with the correct query.
>
>
>
May you should share some more details of exactly what you are expecting
and what is the output/corelation you want in the result of the query.

> Thanks
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com