CODEWITHSUNDEEP
postgresqldifference
rshar
rshar

Reputation: 1477

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

Upvotes: 0

Views: 538

Answers (3)

eijeze
eijeze

Reputation: 151

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;

Upvotes: 1

lemon
lemon

Reputation: 15482

You can directly compute the COUNT on distinct values if you use the DISTINCT keyword inside your aggregation function. Then you can join the queries extracting your two needed values, and use them inside your query to get the output table.

WITH cte AS (
    SELECT new.cnt AS count_new,
           old.cnt AS count_old
    FROM       (SELECT COUNT(DISTINCT id) AS cnt FROM schema1.compound) AS old
    INNER JOIN (SELECT COUNT(DISTINCT id) AS cnt FROM schema2.compound) AS new
            ON 1 = 1
)
SELECT 'compound' AS table_name,
       count_new,
       count_old,
       count_new = count_old AS diff
FROM cte

Upvotes: 0

IamFr0ssT
IamFr0ssT

Reputation: 769

I think you could do something like this:

SELECT 'compound' AS table_name, count_old, count_new, (count_old - count_new) AS diff FROM ( 
    SELECT( 
        (SELECT count(*) FROM (SELECT DISTINCT id FROM schema1.compound)) AS count_old,
        (SELECT count(*) FROM (SELECT DISTINCT id FROM schema2.compound)) AS count_new
    )

It was probably answered already, but it is a subquery/nested query.

Upvotes: 0

Related Questions