SQL query to scan new ID columns for different values than the corresponding originals

query_new_columns_for_diffs.sql

-- this query will probe all tables for any ID columns prefixed with "new_" that are different
-- than the corresponding original columns.
-- it reports the tables containing such columns as they are being checked
DO $$
DECLARE
  temp record;
  stmt varchar;
  recs integer;
BEGIN
  FOR temp IN
    SELECT t1.table_name
         , array_agg(t1.column_name || ' IS NULL OR (' || 
                     t1.column_name || ' IS NOT NULL AND ' || 
                     t2.column_name || ' IS NULL) OR ' ||
                     t1.column_name || ' <> ' || t2.column_name) as column_conditions
      FROM information_schema.columns t1
      JOIN information_schema.columns t2
        ON (t1.table_name = t2.table_name) AND (t1.column_name = 'new_' || t2.column_name)
     WHERE t1.table_name in ('checklist_list_template_attachments',
                             'comments_prostore_files',
                             'contacts_prostore_files',
                             'form_templates',
                             'forms',
                             'pdf_templates',
                             'programs',
                             'alternate_formats',
                             'transcriptions',
                             'received_documents',
                             'report_templates',
                             'prostore_files_revised_estimates',
                             'prostore_files_rfp_proposals',
                             'prostore_files_rfp_requests')
       AND (t1.column_name LIKE 'new_%_id'
        OR t1.column_name = 'new_id')
  GROUP BY 1
  ORDER BY 1
  LOOP
    stmt = 'SELECT COUNT(*) FROM ' || temp.table_name || ' WHERE ' || array_to_string(temp.column_conditions, ' OR ') || ';';
    RAISE NOTICE 'Checking new columns on table %', temp.table_name;
    EXECUTE stmt INTO recs;
    IF recs > 0 THEN
      RAISE WARNING 'There are % diffs!', recs;
    ELSE
      RAISE NOTICE 'No diffs';
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

When there is an error, it looks like this:

NOTICE:  Checking table programs
WARNING:  There are 1 nulls!