PostgreSQL : How to select rows from multiple tables inside different schemas?

Question

I am trying to select all rows from multiple tables inside various schemas.

These are the tables inside different schemas

schema_1-->ABC_table_1,XYZ_table_2
schema_2-->ABC_table_1,JLK_table_2

.
.
schema_N-->ABC_table_1,LMN_table_2

I am trying to select all rows from table_2 from all schemas:

This query is giving me all the tables:

SELECT
    table_schema || '.' || table_name
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
AND
    table_schema NOT IN ('pg_catalog', 'information_schema');

What i need to do is something like

select * from schema_1.XYZ_table_2
Union all
select * from schema_2.JLK_table_2
.
.
schema_2.LMN_table_2```

Laurenz Albe · Accepted Answer · 2022-12-13 03:50:26Z

1

You can do that with \gexec if you use psql:

SELECT string_agg(
          format('SELECT * FROM %I.%I', table_schema, table_name)
          ' UNION ALL '
       )
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
\gexec

answered Dec 13, 2022 at 3:50

Laurenz Albe

62.7k4 gold badges58 silver badges94 bronze badges

Add a comment |

Stack Exchange Network

PostgreSQL : How to select rows from multiple tables inside different schemas?

1 Answer 1

Your Answer

Hot Network Questions

PostgreSQL : How to select rows from multiple tables inside different schemas?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions