Search for null in id column over all tables programmatically

Question

I want to automatically check for null ID's over all id columns in my database, I have to check all tables of which there is about 50,000 so manually doing this is infeasible

Answer 1

This is a function that isn't very elegant, nor optimized, but perhaps it can be a start to get you going. It loops over all tables in a database and in case a table contains a column with name id and if any row value in that column is NULL it will return the table name. The function is quite stupid as it will not check if the column is a primary key, just that its name is id. And I'm certain there is quite a big room for improvement.

CREATE OR REPLACE FUNCTION check_tables_id()
  RETURNS table(tablename TEXT) AS
$BODY$
DECLARE row RECORD;
DECLARE has_id_col BIGINT;
DECLARE has_null BIGINT;

BEGIN       
    FOR row IN
    -- Restrict to specific database and schema if necessary by specifying 
    -- table_catalog='_dbname_' and/or table_schema='_schema_' in WHERE-clause
        SELECT * FROM information_schema.tables
    LOOP
    -- Check if id col exists
    EXECUTE 'SELECT COUNT(*) FROM information_schema.columns 
        WHERE column_name =''id'' 
        AND table_catalog=''' || quote_ident(row.table_catalog) || '''
        AND table_schema=''' || quote_ident(row.table_schema) || '''
        AND table_name=''' || quote_ident(row.table_name) || ''';' INTO has_id_col;
    IF has_id_col > 0 THEN
        -- Check if col ID is null
        EXECUTE 'SELECT count(*) 
        FROM ' ||  quote_ident(row.table_schema) || '.' || quote_ident(row.table_name) || ' 
        WHERE id IS NULL' INTO has_null;
        -- Return table name
        IF has_null > 0 THEN
            RETURN QUERY EXECUTE 'SELECT ''' ||  quote_ident(row.table_schema) || '.' || quote_ident(row.table_name) || '''::TEXT AS tablename';
        END IF;
    END IF;
    END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql';

Just run the function using:

SELECT * FROM check_tables_id();

The result you get is a list like this:

dbase=# select * from check_tables_id();
   tablename    
----------------
 dbase.table3
 dbase.table4
(2 rows)