detect duplicate primary keys.sql

-- POSTGRESQL check for duplicate primary keys due to
-- BUG #11141: Duplicate primary key values corruption
-- http://www.postgresql.org/message-id/[email protected]

-- Select all tables (excluding schema_migrations table)
-- SELECT TABLE_NAME AS tablename
-- FROM information_schema.tables
-- WHERE table_schema = 'public'
--  AND TABLE_NAME != 'schema_migrations'
-- ORDER BY TABLE_NAME;


-- Select count of records with dublicate ids (primary key)
-- SELECT count(*)
--    FROM table_name
--  WHERE table_name.id IN
--    (SELECT table_name.id
--       FROM table_name
--       GROUP BY table_name.id
--       HAVING count(table_name.id)>1)
    

DO $$
DECLARE
    tables CURSOR FOR SELECT table_name as tablename FROM information_schema.tables WHERE table_schema = 'public' and table_name != 'schema_migrations' ORDER BY table_name;
    count int;
BEGIN
    FOR table_record IN tables LOOP
        EXECUTE 'SELECT count(*) FROM ' || table_record.tablename || ' WHERE ' || table_record.tablename || '.id IN (SELECT ' || table_record.tablename || '.id FROM ' || table_record.tablename || ' GROUP BY ' || table_record.tablename || '.id HAVING count(' || table_record.tablename || '.id)>1)' INTO count;
        raise notice '%', count;
    END LOOP;
END$$;