Add constraint to prevent NaN in a table

Question

I have a column that has the form myVar REAL NOT NULL. This column should only ever contain actual numbers. What kind of constraint can I add to prevent myVar from becoming NaN?

Answer 1

Usually you need a special function to detect if a value is NaN (similar to requiring is null and is not null tests for SQL's null) but in PostgreSQL, NaN = NaN is true:

[...] In order to allow numeric values to be sorted and used in tree-based indexes, PostgreSQL treats NaN values as equal, and greater than all non-NaN values.

So a simple CHECK constraint with a direct comparison is sufficient:

check (myvar <> 'NaN')

You can include a cast if you like but it isn't necessary:

check (myvar <> 'NaN'::real)
check (myvar <> 'NaN'::numeric)