Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
3  
What have you tried? –  dezso Jun 22 '12 at 16:06
    
nothing as of yet, i'm not that good with postgresql –  Jharwood Jun 22 '12 at 16:10
4  
Then have a look at plpgsql and information_schema views, namely tables and columns. –  dezso Jun 22 '12 at 16:18

1 Answer 1

up vote 2 down vote accepted

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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.