PostgreSQL中无法将NaN转换为0的问题求助

Let's break down why your update statement is failing and how to fix it properly.

Why You're Getting the Error #

Your original query tries to compare a numeric column to the string literal 'NaN':

UPDATE anytable SET "anycolumn" = 0 WHERE "anycolumn" = 'NaN';

PostgreSQL attempts to implicitly convert the string 'NaN' to match the column's data type, but a quirk in type conversion priority is making it try to convert 'NaN' to an integer—which isn't valid, hence the ungültige Eingabesyntax für Typ integer: »NaN« error and SQL state 22P02 (invalid text representation).

Even if it converted to numeric correctly, comparing with = 'NaN' still wouldn't work as expected: in PostgreSQL, NaN is not equal to any value, including itself. So WHERE "anycolumn" = NaN would never match any rows.

Correct Solutions #

There are two reliable ways to target NaN values in a numeric column:

1. Use the IS NAN Operator (Recommended)

PostgreSQL has a dedicated operator built just for detecting NaN values, which avoids the equality comparison issue entirely:

UPDATE anytable 
SET "anycolumn" = 0 
WHERE "anycolumn" IS NAN;

This is the cleanest and most efficient method—no type conversion hoops, just direct, readable logic.

2. Explicitly Cast the String to numeric

If for some reason IS NAN isn't available (unlikely in modern PostgreSQL versions), you can explicitly cast 'NaN' to a numeric value to match your column type:

UPDATE anytable 
SET "anycolumn" = 0 
WHERE "anycolumn" = 'NaN'::numeric;

Note: While this works, IS NAN is still the better choice for readability and avoiding edge cases.

Key Takeaway #

Always use IS NAN to check for NaN values in PostgreSQL numeric columns. Equality comparisons (= NaN) will never return true, and comparing to uncast string literals can trigger the type conversion error you ran into.

内容的提问来源于stack exchange，提问作者Data Science