patternsqlMinor

Find all `NaN` values in a postgresql database

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

postgresqlallnandatabasefindvalues

Problem

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.

Solution

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

Code Snippets

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

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

NOTICE:  Found Column n  in Table x
DO

Context

StackExchange Database Administrators Q#221108, answer score: 4

Revisions (0)

No revisions yet.