3

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.

4

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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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