CODEWITHSUNDEEP
postgresqlmadlib
Nisa Anwaar
Nisa Anwaar

Reputation: 95

Run Query on All Schemas Postgres

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

Upvotes: 8

Views: 7096

Answers (2)

Fayiz Fareed
Fayiz Fareed

Reputation: 35

Use this query

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

Upvotes: 0

Reto Buchli
Reto Buchli

Reputation: 144

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?

Upvotes: 11

Related Questions