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

Floating point

I bought Mastering Postgres because I use Postgres a lot and wanted to support Aaron and his content. I already learned more than I expected and have been very impressed with the quantity and work Aaron put in.Timo Strackfeldt

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

In this video, we explore fractional numbers with variable precision, focusing on how floating point numbers work. I explain that while floating numbers are fast and support fractions, they can be imprecise, making them suitable for approximating data like temperature but not ideal for financial calculations. We compare different types like "real" and "double precision" to see how they offer varying levels of speed and accuracy, which helps us decide which one best fits our needs.

Up next is fractional numbers with variable precision that are inexact, but quite fast.

So remember, integers, whole numbers, perfectly precise, very, very fast. Numeric supports fractions, very, very precise, very slow.

Floating point numbers are all the way on the right hand side, supports fractions, inexact, but very, very fast. So let me show you a few of the ways that you can declare it and then I'll try to prove to you that it's both fast and somewhat inaccurate.

The example that I'm working with here is some sort of IOT, Internet of Things sensor reading table, so you have a sensor name and then a reading. And I feel like this is actually a pretty good example. When I say that floats are approximations, that doesn't mean that they're bad, that doesn't mean that they're wrong, it just means it's an approximation, which is oftentimes good enough, not when dealing with financial data. But when dealing with temperature or barometric pressure, whatever that is, a pretty good approximation is good enough. So you have a few options here. You could say real, and you could say double, but for some reason you gotta type double precision.

So let's talk about these. If you hop down here, a real and a double precision are both floating point approximations. A real is a 4 byte and it goes from 1, how do you do this? 1E-37 I think is the correct notation, all the way to 1E37, just fundamentally a huge, a huge, huge range. And you have at least 6 digits after the decimal point of precision.

Now, if we hop down here, you'll see that it is double, which means it is 8 bytes. I'm gonna go ahead and add the word bytes up here, so we remember it's 8 bytes down here, and this is just a magnificent range, 1E-307 all the way to 1E+308 with at least 15, 15 digits after the decimal point of precision. Those are the two types of approximations that you have.

There are a bunch of aliases that we'll look at, but you need to remember a real is 4 bytes and a double, double precision is 8 bytes. So depending on what range you need or potentially what precision you need after the decimal, that will dictate which of these two columns you should use. And just like integers we have, you have float 4, which is legit. And so then if you were to hop over here and you were to do D, what did we name that, d real_example, you would see that it was transformed into real. So we can drop table, real_example, and another option that you have is float 1 through 24.

In other databases you can pass through this value. In fact, I think in MySQL it's also ignored the same way that it's ignored here in Postgres. So 1 through 24 is going to lead to a real, and 25 through, I think, I actually don't remember, maybe 53 leads to a double. And so if we were to put, let's put just so we do reading_49 and we were to make it a float(49) and we were to run that and then hop back over here, you'll see it has transformed into a double precision. So you also have float8 in the same way that you have int 2, int 4 and int 8.

All right, enough with the aliases, I just tell you those aliases so that when you see them in the wild, you have a place in your mind to slot that information, but typically you're gonna see real and double precision, but who knows, maybe you'll see float8 somewhere.

We're actually gonna look at float8 example right here 'cause I wanna show you, I wanna try to show you that the floats can be imprecise when you're doing mathematical operations on them and they're quite fast when compared to numeric. So I promised you that they are fast and that they can lose precision, let's see if we can prove it. So over here we have 7.0 cast to a float8. And so you could cast this to a double precision, but float8 is nice and compact, so we're gonna cast it to a float8 and then multiply it times 2.0 divided by 10.0. And if we run that, you'll see we get 1.400000001. So I mean, is it, is it right? Not really. Is it close? Yeah, super close. But if you were to compare this to 1.4, it's just, it's gonna say, it's gonna say no, that's just not the case. And so when you are doing mathematical operations on floats or with floats, you do have to account for, you do have to account for that in precision.

So instead of doing a strict equality, you might have to do, you might have to subtract 1.4. So let's say is it equal to 1.4? No, but is it less than, you know, 0.001 off? Ah, okay, we're gonna count that as right. So this is a function of floating point numbers. This is not a problem with Postgres, this is the way that it's designed. This is the way floating point numbers operate. We're trying to represent, we're trying to represent something that is unrepresentable in the underlying storage and so you end up with some weird stuff. Now that is kinda right, kinda wrong.

If you were to cast it to numeric, super right. So that's what I'm talking about when I'm talking about precision. Floats, pretty good, numeric, definitely good. Now, what about speed? Don't freak out, there's some stuff here I'm gonna show you. I'm gonna show you some of the stuff. So if we look at this, you'll see we are using this cool, very cool in fact, generate_series function that generates an entire table.

Let me just show you what that is. Select * from generate_series num, and instead of whatever that is, what is that, 20 million? Yeah, instead of 20 million, well let's just do 20. And so what this does is it generates a series, they should have named it that. It generates a series between 1 and 20. So what we're doing here without having to create, you know, a bunch of tables or anything like that, we're gonna cast this to a float and then divide it and then cast it to a float. We're just doing a little bit of math just for giggles and then we're gonna cast this to a numeric, divide it and cast it to a numeric. And what we're trying to do is across 20, yeah, 20 million rows, we're trying to figure out is there a noticeable difference?

Because I keep telling you that there is. So we're just gonna run this and for a float we get 2.3, not a very scientific benchmark, but hopefully it's big enough that we'll see a pretty big difference. Hey, I feel good about that, 4.5. That is twice as slow or twice as fast, depending on what your frame of reference is. So if we run the float8 again, 2.3, and we run the numeric again, we see that it is 4.6.

Again, it sure does look pretty accurate to me, but you see it's a lot slower. So this is almost rounding out our discussion about numbers. We're gonna do money in the next one, but we still have this idea in our head between integer, numeric and floating point. Perfectly accurate, perfectly accurate, approximation. Really fast, kinda slow, really fast. And so pick the one that works for you.

Hopefully we have proven that all of those underlying fundamental assumptions are accurate and now you are well equipped to pick the one that matches your data and your use case.