Use of array_agg and array string on inner query

On Thu, May 19, 2016 at 1:09 AM shankha <shankhabanerjee@gmail.com> wrote:

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 query

SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2 produces
multiple rows. Since you are calling the aggregate function on the result
set and not as part of the expression, you are not able to get single row
as an output.

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;

This would work since the aggregate function has been used on the column.

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.

May you should share some more details of exactly what you are expecting
and what is the output/corelation you want in the result of the 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

--
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

The original table is :

c1 c2 c3
1 10
2 20 10
3 20 10

So c3 of row 3 and row 2 are equal to c2 of row 1.

The output I am looking for is :
c1 | array_to_string
----+-----------------
1 | 2,3
2 |
3 |
(3 rows)

How Can I modify this query :

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;

to get me the output desired.

Thanks
Shankha Banerjee

On Wed, May 18, 2016 at 1:57 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:

On Thu, May 19, 2016 at 1:09 AM shankha <shankhabanerjee@gmail.com> wrote:

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 query

SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2 produces
multiple rows. Since you are calling the aggregate function on the result
set and not as part of the expression, you are not able to get single row as
an output.

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;

This would work since the aggregate function has been used on the column.

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.

May you should share some more details of exactly what you are expecting and
what is the output/corelation you want in the result of the 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

--
--
Best Regards
Sameer Kumar | DB Solution Architect
ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

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

On Wed, May 18, 2016 at 1:07 PM, shankha <shankhabanerjee@gmail.com> wrote:

/* 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 following adjustments should work:​

​array_to_string(select array_agg(t3.c1) FROM ... WHERE t2.c1 = t1.c1), ','

​The array_agg needs to moved into the subquery - which causes an implicit
GROUP BY to be added to the subselect thus ensuring only one row is
returned for processing by the array_to_string function.

​You need to add the where clause to make the subquery correlate to the
outer query.​

​You may need to play with it a bit as I didn't try running your example.​

David J.

On Thu, 19 May 2016, 2:07 a.m. shankha, <shankhabanerjee@gmail.com> wrote:

The original table is :

c1 c2 c3
1 10
2 20 10
3 20 10

So c3 of row 3 and row 2 are equal to c2 of row 1.

The output I am looking for is :
c1 | array_to_string
----+-----------------
1 | 2,3
2 |
3 |
(3 rows)

How Can I modify this query :

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;

Move array_agg call around the column name instead of calling it on the
select output.

The 4th query you have used seems to be working except that it 'kind of'
does a cross product or lateral join. You might want to use a CTE instead
if bested select and use that with OUTER JOIN or may be in the inner query
use a correlated where clause (where t1.c2=t2.c2)

to get me the output desired.

Thanks
Shankha Banerjee

On Wed, May 18, 2016 at 1:57 PM, Sameer Kumar <sameer.kumar@ashnik.com>
wrote:

On Thu, May 19, 2016 at 1:09 AM shankha <shankhabanerjee@gmail.com>

wrote:

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 query

SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2 produces
multiple rows. Since you are calling the aggregate function on the result
set and not as part of the expression, you are not able to get single

row as

an output.

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;

This would work since the aggregate function has been used on the column.

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.

May you should share some more details of exactly what you are expecting

and

what is the output/corelation you want in the result of the 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

--
--
Best Regards
Sameer Kumar | DB Solution Architect
ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

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

--
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

I cannot move the array_agg to around the column name. It has to work
as a inner query.

I will try out your other suggestion.
Thanks
Shankha Banerjee

On Wed, May 18, 2016 at 2:26 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:

On Thu, 19 May 2016, 2:07 a.m. shankha, <shankhabanerjee@gmail.com> wrote:

The original table is :

c1 c2 c3
1 10
2 20 10
3 20 10

So c3 of row 3 and row 2 are equal to c2 of row 1.

The output I am looking for is :
c1 | array_to_string
----+-----------------
1 | 2,3
2 |
3 |
(3 rows)

How Can I modify this query :

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;

Move array_agg call around the column name instead of calling it on the
select output.

The 4th query you have used seems to be working except that it 'kind of'
does a cross product or lateral join. You might want to use a CTE instead if
bested select and use that with OUTER JOIN or may be in the inner query use
a correlated where clause (where t1.c2=t2.c2)

to get me the output desired.

Thanks
Shankha Banerjee

On Wed, May 18, 2016 at 1:57 PM, Sameer Kumar <sameer.kumar@ashnik.com>
wrote:

On Thu, May 19, 2016 at 1:09 AM shankha <shankhabanerjee@gmail.com>
wrote:

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 query

SELECT t3.c1 FROM s.t1 as t3 JOIN s.t1 as t2 ON t3.c3 = t2.c2 produces
multiple rows. Since you are calling the aggregate function on the
result
set and not as part of the expression, you are not able to get single
row as
an output.

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;

This would work since the aggregate function has been used on the
column.

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.

May you should share some more details of exactly what you are expecting
and
what is the output/corelation you want in the result of the 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

--
--
Best Regards
Sameer Kumar | DB Solution Architect
ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

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

--
--
Best Regards
Sameer Kumar | DB Solution Architect
ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

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

On Wed, May 18, 2016 at 2:30 PM, shankha <shankhabanerjee@gmail.com> wrote:

I cannot move the array_agg to around the column name. It has to work
as a inner query
​.

​The following form is used to make an array from a subquery:

SELECT ARRAY(SELECT i FROM ( VALUES (1), (2), (3) ) vals (i) );​
​
​

http://www.postgresql.org/docs/9.5/static/sql-expressions.html

4.2.12; last example

Not the most obvious place...

David J.​

I got the query:

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;

Thanks for all the help.

Thanks
Shankha Banerjee

On Wed, May 18, 2016 at 2:40 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Wed, May 18, 2016 at 2:30 PM, shankha <shankhabanerjee@gmail.com> wrote:

I cannot move the array_agg to around the column name. It has to work
as a inner query
.

The following form is used to make an array from a subquery:

SELECT ARRAY(SELECT i FROM ( VALUES (1), (2), (3) ) vals (i) );

http://www.postgresql.org/docs/9.5/static/sql-expressions.html

4.2.12; last example

Not the most obvious place...

David J.

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