Find all `NaN` values in a postgresql database

Question

Numerics and floats can store NaN https://www.postgresql.org/docs/current/static/datatype-numeric.html

I have a working program with a PostgreSQL database that does not have any constraint about NaN.

I want to know which fields of which tables contains NaN (looking at some schemas, not at public schema).

Is there an automatic way to do that?

Later I add a constraint to that fields of that tables.

Answer 1

You may loop through the Information_schema.columns and check tables dynamically.

DO $$
DECLARE
rec RECORD;
v_found BOOLEAN ;
BEGIN

for rec IN (   SELECT column_name,table_name,table_schema 
               FROM  information_schema.columns 
             WHERE  data_type IN ( 'numeric', 'real', 'double precision' )  )
LOOP
   v_found := FALSE;
   EXECUTE format ( 'select TRUE  FROM %I.%I WHERE %I = %L LIMIT 1' ,rec.table_schema,rec.table_name,rec.column_name,'NaN') INTO v_found;

   IF v_found = TRUE
   THEN
        RAISE NOTICE 'Found Column %  in Table %.%' , rec.column_name,rec.table_schema,rec.table_name;
   END IF;

END LOOP;

END$$;

Testing

knayak=# create table x as select CAST( 'Nan' as NUMERIC) as n ;
SELECT 1

Result

NOTICE:  Found Column n  in Table x
DO

Answer 2

Queriable version

This is a rewrite of Kaushik Nayak solution. The adventage is that is a resultset that can be used in SQL for others propuses (for example creating sentences for changing the NaN with null or for creating the constraints):

CREATE OR REPLACE FUNCTION find_columns_with_nan(p_having_null boolean) RETURNS SETOF information_schema.columns
  LANGUAGE plpgsql as
$body$
DECLARE
  rec RECORD;
  v_found BOOLEAN;
BEGIN
  FOR rec IN (SELECT *
                FROM information_schema.columns 
                WHERE data_type IN ( 'numeric', 'real', 'double precision' )  )
  LOOP
    v_found := FALSE;
    EXECUTE format('SELECT TRUE FROM %I.%I WHERE %I = %L LIMIT 1',rec.table_schema,rec.table_name,rec.column_name,'NaN') 
      INTO v_found;
    IF v_found IS TRUE = p_having_null THEN
      RETURN NEXT rec;
    END IF;
  END LOOP;
END
$body$;

SELECT table_schema,table_name,column_name FROM find_columns_with_nan(true);