Run Query on All Schemas Postgres

Question

We have a around 100+ schema maintained in PostgreSQL. Now we want to query on all schema, is there any way to do that? other than views, procedures and union all? Any postgres functions which let you query on multiple schemas

Answer 1

The following catalog query will produce valid queries for every table on all schemas of your database. You can copy this to a valid SQL file.

SELECT 'SELECT * FROM ' || table_schema || '.' || table_name || ';' AS query 
FROM information_schema.tables 
WHERE table_schema IN
(
    SELECT schema_name 
    FROM information_schema.schemata
    WHERE schema_name NOT LIKE 'pg_%' AND schema_name != 'information_schema'
);

Does this help?

Answer 2

Use this query

set search_path = schema_name_1, schema_name_2, schema_name_3, schema_name_4;
SELECT * FROM table_1;