CODEWITHSUNDEEP
sqlpostgresqlreplacenan
elixir
elixir

Reputation: 979

Return Null in a query, instead of NaN

in PostgreSQL, I would like a query to return Null, or empty value, instead of NaN (this NaN was inserted by python's pandas to fill an empty value).

Exemple: Select name, age From "People"

I would like to get:

John 24

Emily

Laura 50

Instead of:

John 24

Emily NaN

Laura 50

Upvotes: 6

Views: 7510

Answers (3)

Simo Kivistö
Simo Kivistö

Reputation: 4463

If you would like null-values instead of 'NaN' you could simply use:

SELECT name, NULLIF(age, 'NaN') From "People"

Upvotes: 10

Raging Bull
Raging Bull

Reputation: 18747

Try using REPLACE:

Select name, REPLACE(age, 'NaN')
From People

It will replace the value 'NaN' with an empty string if age field contains it.

Upvotes: 0

AFract
AFract

Reputation: 9680

I think you need to use : 

SELECT name, coalesce(age, '') as age From "People"

Coalesce replaces a null value (first parameter) by the second parameter (here, an empty string).

If you need to test more complex cases than NULLs, you could also use CASE/WHEN : 

SELECT name, CASE WHEN age IS NULL THEN '' ELSE age END AS age from "People"

but COALESCE has better readibility for simple null handling.

Upvotes: 0

Related Questions