Report the differences between two tables

CREATE TEMP TABLE v1 AS SELECT * FROM (VALUES
  (1, 'ann', 10, 1.5), (2, 'bob', 5, NULL), (3, 'cy', 7, 2.0), (4, 'di', 1, 3.0), (6, 'fay', 3, 1.0), (NULL, 'nk', 1, 1.0)) t(id, name, qty, price);
CREATE TEMP TABLE v2 AS SELECT * FROM (VALUES
  (1, 'ann', 12, 1.5), (2, 'bob', 5, 2.5), (3, 'cyd', 8, 2.0), (5, 'ed', 2, 4.0), (6, 'fay', 3, 1.0), (NULL, 'nk', 1, 1.0)) t(id, name, qty, price);
-- the key must be unique on each side, or a record is compared more than once; a NULL key matches nothing, not even itself
CREATE TEMP TABLE repeated_keys AS
SELECT 'v1' AS side, id FROM v1 WHERE id IS NOT NULL GROUP BY id HAVING count(*) > 1
UNION ALL SELECT 'v2', id FROM v2 WHERE id IS NOT NULL GROUP BY id HAVING count(*) > 1;
-- every column as text, one row per key and column
CREATE TEMP TABLE old_cells AS
FROM (SELECT id, name::VARCHAR AS name, qty::VARCHAR AS qty, price::VARCHAR AS price FROM v1)
UNPIVOT INCLUDE NULLS (val FOR col IN (name, qty, price));
CREATE TEMP TABLE new_cells AS
FROM (SELECT id, name::VARCHAR AS name, qty::VARCHAR AS qty, price::VARCHAR AS price FROM v2)
UNPIVOT INCLUDE NULLS (val FOR col IN (name, qty, price));
-- cell by cell: one row for each cell that differs; IS DISTINCT FROM also sees NULL turning into a value
CREATE TEMP TABLE cell_report AS
SELECT id, col, o.val AS old_value, n.val AS new_value
FROM old_cells o JOIN new_cells n USING (id, col) WHERE o.val IS DISTINCT FROM n.val;
-- column by column: one row for each column compared
CREATE TEMP TABLE column_report AS
SELECT col, count(*) AS compared, count(*) FILTER (WHERE o.val IS DISTINCT FROM n.val) AS differing
FROM old_cells o JOIN new_cells n USING (id, col) GROUP BY col;
-- summary figures: one row for the two tables
CREATE TEMP TABLE summary AS
SELECT (SELECT count(*) FROM v1) AS rows_v1, (SELECT count(*) FROM v2) AS rows_v2,
       count(*) AS matched, count(*) FILTER (WHERE a IS DISTINCT FROM b) AS changed, count(*) FILTER (WHERE a IS NOT DISTINCT FROM b) AS identical,
       (SELECT count(*) FROM v1 ANTI JOIN v2 ON v1.id = v2.id) AS only_in_v1, (SELECT count(*) FROM v2 ANTI JOIN v1 ON v1.id = v2.id) AS only_in_v2
FROM v1 a JOIN v2 b ON a.id = b.id;
SELECT * FROM cell_report ORDER BY id, col;
SELECT * FROM column_report ORDER BY col;
SELECT * FROM summary;
SELECT CASE WHEN ((SELECT count(*) = 0 FROM repeated_keys)
  AND (SELECT list((id, col, old_value, new_value) ORDER BY id, col) IS NOT DISTINCT FROM [(1, 'qty', '10', '12'), (2, 'price', NULL, '2.5'), (3, 'name', 'cy', 'cyd'), (3, 'qty', '7', '8')] FROM cell_report)
  AND (SELECT map_from_entries(list((col, differing) ORDER BY col)) = MAP {'name': 1, 'price': 1, 'qty': 2} AND min(compared) = 4 AND max(compared) = 4 FROM column_report)
  AND (SELECT rows_v1 = 6 AND rows_v2 = 6 AND matched = 4 AND changed = 3 AND identical = 1 AND only_in_v1 = 2 AND only_in_v2 = 2 FROM summary)) THEN 'as expected' ELSE error('the report should hold 4 cells (qty of 1 and 3, the price of 2 turning from NULL to 2.5, the name of 3), 4 compared records in each column, and 4 matched records of which 3 changed, with ids 4 and 5 and the two NULL-key rows each unmatched') END AS verdict;