Re: Use of array_agg and array string on inner query - Mailing list pgsql-general

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 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;

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.

Thanks


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general