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

Do you know any extensions which can do that? I tried madlib, couldnt find any — Nisa Anwaar, Oct 12 '18 at 14:08

score 9 · Accepted Answer · answered Oct 13 '18 at 17:12

9

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?

answered Oct 13 '18 at 17:12

Reto Buchli

124
6

1

Is there a way to tell Postgres to execute the generated queries on the spot? – payne Dec 03 '21 at 22:37
re: Is there a way to tell Postgres to execute the generated queries on the spot? in psql use \gexec at the end of the query instead of semicolon – srk Mar 14 '22 at 00:02

score 0 · Answer 2 · answered Apr 18 '23 at 20:22

0

Use this query

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

answered Apr 18 '23 at 20:22

Fayiz Fareed

35
2
9

Run Query on All Schemas Postgres

2 Answers2