How to use string aggregation on concatenated result subquery in Postgres?

Question

I am trying to use string aggregation on the results of a subquery that contains concatenated results. I would like to create a long text string containing the qualified name "schema.table" for a DB schema I have in Postgres. Do you know how to achieve it? Below is the code I have come up with (not working), it says more than one row returned by a subquery used as an expression.

SELECT string_agg(
    (SELECT CONCAT(table_schema, '.', table_name) table_schema_name
    FROM information_schema.tables
    WHERE table_schema = 'name_of_schema'), ' ')
FROM information_schema.tables
WHERE table_schema = 'name_of_schema'

what I have now is:

| table_schema_name | 

name_of_schema.table1
name_of_schema.table2
name_of_schema.table3

What I would like to achieve is:

|                          agrreagated field                     |

name_of_schema.table1 name_of_schema.table2 name_of_schema.table3

DrCabry · Accepted Answer · 2018-01-19 14:19:22Z

2

This query should do the task you're asking for:

SELECT string_agg(CONCAT(table_schema, '.', table_name), ' ')
    FROM information_schema.tables
    WHERE table_schema = 'schema_name'

edited Jan 19, 2018 at 14:19

answered Jan 18, 2018 at 15:51

DrCabry

665 bronze badges

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

1 Comment

user3523583 Over a year ago

Yes this one did the job.

clemens · Accepted Answer · 2018-01-18 15:50:38Z

1

You should either use the query as table expression in your outer select:

SELECT string_agg(s.table_schema_name, ' ') FROM (
    SELECT CONCAT(table_schema, '.', table_name) AS table_schema_name
    FROM information_schema.tables
    WHERE table_schema = 'public'
) s

or simply avoid the sub-select:

SELECT string_agg(CONCAT(table_schema, '.', table_name), ' ')
FROM information_schema.tables
WHERE table_schema = 'public';

answered Jan 18, 2018 at 15:50

clemens

17.9k12 gold badges52 silver badges71 bronze badges

Collectives™ on Stack Overflow

How to use string aggregation on concatenated result subquery in Postgres?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related