Search code examples
postgresqldifference

Calculate difference between the row counts of tables in two schemas in PostgreSQL

I have two table with same name in two different schemas (old and new dump). I would like to know the difference between the two integration.

I have two queries, that gives old and new count:

select count(*) as count_old from(
        SELECT 
            distinct id
        FROM 
            schema1.compound)q1
    

select count(*) as count_new from(     
        SELECT 
            distinct id
        FROM 
            schema2.compound)q2

I would like have the following output.

table_name  count_new count_new diff
compound    4740      4735      5

Any help is appreciated. Thanks in advance

Solution 
  • with counts as (
    select
        (select count(distinct id) from schema1.compound) as count_old,
        (select count(distinct id) from schema2.compound) as count_new
)
select
    'compound' as table_name,
    count_old,
    count_new,
    count_old - count_new as   diff
from counts;