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