Use of array_agg and array string on inner query

From: shankha <shankhabanerjee(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Use of array_agg and array string on inner query
Date: 2016-05-18 17:07:48
Message-ID: CAO_L6qHkxESSvSyY9Fogyvj5FV2j5OrhAh5FHvvneQGKB=a-Ng@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pierre Chevalier Géologue 2016-05-18 17:51:38 Re: Thoughts on "Love Your Database"
Previous Message David G. Johnston 2016-05-18 16:47:58 Re: Thoughts on "Love Your Database"