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);