Use of array_agg and array string on inner query

Question

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), ',') 
 FROM s.t1 t1
 GROUP BY c1;

Michael Ambrose · Accepted Answer · 2016-05-18 13:43:45Z

1

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;

edited May 18, 2016 at 13:43

answered May 18, 2016 at 1:32

Michael Ambrose

1,0607 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

gudge Over a year ago

Both don't seem to work: 1. ERROR: function array_agg(bigint, unknown) does not exist LINE 10: ARRAY_TO_STRING((SELECT ARRAY_AGG(t3.c1, ',') FROM s.t1 ... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. 2.psql:/tmp/aggregate.sql:43: ERROR: function string_agg(bigint, unknown) does not exist LINE 17: (SELECT STRING_AGG(t3.c1, ',') FROM s.t1 as t3 JOIN s.t1... HINT: No function matches the given name and argument types. You might need to add explicit type casts. Thanks fo the help.

Michael Ambrose Over a year ago

Sorry, I wrote this on my phone last night. I put a few parameters in the wrong spot. Go ahead and try both of these again.

gudge Over a year ago

c1 | c2 | string_agg ----+----+------------ 2 | 20 | 2,3 1 | 10 | 2,3 3 | 30 | 2,3 I get output from both queries.

gudge Over a year ago

No. I was looking for: 1 | 2,3 2 | 3 | 2, 3 should have entries in the third colmn as 2.3

gudge Over a year ago

I am looking for output as Query 2

|

Collectives™ on Stack Overflow

Use of array_agg and array string on inner query

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related