Question 174450 in PostgreSQL Question posted in
The official documentation can be found here.

How maintain order in postgresql result from two different schema

I have a requirement. I need to take count of same tables from two different schema. The required output should be like (see screenshot here)

schema table_name count
sch1 table1 1000
sch2 table1 500
sch1 table2 3000
sch2 table2 1000

this is to take count difference of table from schema1 and schema2. so i get an output in above fashion i can use LAG() or LEAD function. Please help

I tried many ways. But all result I’m getting related to group by usage only which will not maintain the expected fashion.

Tags:

2

Answers


  1. 0

    Probably the most straightforward way of doing this is by making UNION ALL of 4 subqueries, each retrieving your counts from your four tables.

    SELECT 'sch1' AS schema, 'table1' AS tablename, COUNT(*) AS cnt FROM sch1.table1
UNION ALL
SELECT 'sch2',           'table1',              COUNT(*)        FROM sch2.table1
UNION ALL
SELECT 'sch1',           'table2',              COUNT(*)        FROM sch1.table2
UNION ALL
SELECT 'sch2',           'table2',              COUNT(*)        FROM sch2.table2
    Login or Signup to reply.
  2. 0

    When the list of tables cannot be hard-coded (say you need to iterate on information_schema.tables), dynamic SQL must be used. For each table, a query must be generated, and then executed in a second step.

    You may use this function to generate and execute the row count of a given table:

    CREATE OR REPLACE FUNCTION row_count(schema_name text, table_name text)
 RETURNS bigint LANGUAGE plpgsql
AS $function$
DECLARE
 cnt bigint;
BEGIN
   EXECUTE format('select count(*) from %I.%I',
              schema_name, table_name)
    INTO cnt;
   RETURN cnt;
END
$function$

    Then use it in the following query to get in a single result set all row counts of tables that exist with the same name in multiple schemas:

    WITH list(s,t) as (select table_schema, table_name
  from information_schema.tables
  where table_type='BASE TABLE'
    and table_schema in ('schema1', 'schema2')
)
SELECT l1.s, l1.t, row_count(l1.s, l1.t)
  FROM list l1 JOIN list l2 ON l1.t=l2.t and l1.s<>l2.s;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search