Validate data correctness after migration

Learn how to validate the correctness of your data after migrating to ClickHouse Managed Postgres

Comparing counts of all tables

A simple way to validate the correctness of your data after migration is to compare the counts of all tables in the source and target databases. You can do this by running the following query on both databases:

SELECT table_name, 
       (xpath('/row/cnt/text()', xml_count))[1]::text::bigint AS row_count
FROM (
  SELECT table_name, 
         query_to_xml('SELECT count(*) AS cnt FROM ' || quote_ident(table_name), false, true, '') AS xml_count
  FROM information_schema.tables
  WHERE table_schema = 'public'
) t
ORDER BY table_name;