Introduction

Data Types

Advanced Data Types

Indexing

Advanced Indexing

Understanding Query Plans

Generating Results

Advanced SQL

Full Text Search

JSON

Coming Soon

Operating Postgres

Coming Soon

Popular Extensions

Coming Soon

Bonus interviews

Watch for free

Enter your email below to watch this video

Data Types

NaNs and infinity

Window function ðŸ˜Š. CTE with window function ðŸ˜®. Hierarchical recursive CTE ðŸ¤¯. Recommended all the way. Canâ€™t wait to see the rest of the videos!M Wildan Zulfikar

Shorten dev cycles with branching and zero-downtime schema migrations.

In this video, I explain how numeric and float types in databases can contain special values like NaN (Not a Number) and infinity, while integers cannot. I discuss how these special values behave in calculations, like how NaN equals other NaNs and how infinity minus infinity results in NaN. I highlight that knowing about NaN and infinity can be useful, especially in understanding how numeric data without specific bounds operates.

We're gonna take a little break from specific data types 'cause I want to show you a few oddities about some of these numeric types. Just real quick, this will be a fast one.

If we did select NaN and cast it to a numeric, we would get a NaN, we get a not a number. Floats also have not a number, but integers do not have not a number. So a not a number is abbreviated as a NaN, and numerics and floats have them. Now, numerics and floats also have infinity. Integers do not because, by definition, integers are bounded and infinity is unbounded.

However, an open-ended numeric does have infinity, but a closed numeric with a range does not have an infinity. It also has a negative infinity, and you could change it to inf if you don't feel like typing. Now, I believe floats also have infinities and negative infinities. One interesting thing about NaNs is not a number, and I believe this is Postgres specific.

If you cast this to a numeric, all NaNs are equal to all other NaNs, and all infinities, infinities are equal to all other infinities. When it comes to sorting, NaNs are huge. So if we say NaN is greater than, you know, a big numeric, if we can type, we'll say cast as numeric, NaN is greater than the other numbers. Some things work.

So like infinity, infinity plus infinity, a little different style there, infinity plus infinity is infinity, but infinity minus infinity is not a number. Infinity plus 1 is infinity. That checks out. Infinity minus 1 is still infinity. So there's some deep math, there's some deep math or I guess that's more philosophy in there. But NaN and infinity do exist.

If you need something that is arbitrarily large or arbitrarily small, something that is unbounded and unknowably large and unbounded and unknowably small, you have infinity and negative infinity. Those actually can serve a purpose. Putting a NaN purposefully into your dataset, I can't think of a very good example. NaN is real, it exists, it represents not a number, and so there might be a very valid use case for that.

I don't fully know what it is because there are times when you can turn a NaN back into a real number. So a NaN plus a 1 is gonna give you a NaN. But if you raise it to the power of 0, it's going to give you a 1. Because any number raised to the power of 0 gives you a 1.

So incredibly useful? It is not. Good to know that it exists? Yeah, I think so. I think infinity and negative infinity are probably more useful. Knowing that NaN exists? Hmm, maybe helpful.

Remember, they only exist in the unbounded columns, which is numeric without a precision in scale or the floats in the integers. They do not exist.