Thread: Use of array_agg and array string on inner query

Use of array_agg and array string on inner query

From

shankha

Date:

18 May 2016, 20:08:32

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

Re: Use of array_agg and array string on inner query

From

Sameer Kumar

Date:

18 May 2016, 20:57:49

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

Re: Use of array_agg and array string on inner query

From

shankha

Date:

18 May 2016, 21:07:28

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

Re: Use of array_agg and array string on inner query

From

"David G. Johnston"

Date:

18 May 2016, 21:09:49

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.

Re: Use of array_agg and array string on inner query

From

Sameer Kumar

Date:

18 May 2016, 21:26:30

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

Re: Use of array_agg and array string on inner query

From

shankha

Date:

18 May 2016, 21:31:09

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

Re: Use of array_agg and array string on inner query

From

"David G. Johnston"

Date:

18 May 2016, 21:40:36

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.

Re: Use of array_agg and array string on inner query

From

shankha

Date:

19 May 2016, 16:03:17

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