0

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

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.