gudge gudge - 6 months ago 18
SQL Question

Use of array_agg and array string on inner query

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.

Answer:

SELECT c1, c2,
ARRAY_TO_STRING((SELECT ARRAY_AGG(t2.c1) FROM s.t1 as t2 WHERE t2.c3 = t1.c2), ',') AS snapshots
FROM s.t1 t1
GROUP BY c1;

Answer

The array_agg function is an aggregate function that needs to be used on columns, not a set.

Try this:

/* 3. */ 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;

Or use the string_agg function:

/* 3. */ SELECT c1, c2,
(SELECT STRING_AGG(t3.c1::text, ',') FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2)
FROM s.t1 t1
GROUP BY c1;