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

Numeric

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, I explain the differences between integer, decimal (numeric), and floating point number types, emphasizing how they handle precision and speed. I recommend using decimals for precise financial calculations involving fractions, as they retain accuracy but are slower compared to other types. I demonstrate how to use precision and scale parameters with numeric types to control the number of digits, and I highlight why proving concepts through examples is valuable for learning.

We just covered integers. And so now we're gonna look at fractional numbers. And I think it might be helpful to think about this kind of on a spectrum so we can compare and contrast. So on the far left, we've got integer. They are perfectly accurate. All the math is always going to tie out. You won't lose any precision over there. They are very fast. They only support whole numbers. So that is integers.

Moving one step over is what we're talking about right now, which is decimals or numerics. They support fractions of numbers. They are perfectly accurate, so you're not gonna lose any precision. They are, however, extremely slow. When it comes to operating on these numbers, it is very, very slow compared to integers and compared to one step over, which is floating point numbers. They support fractional numbers. You lose precision. It is not perfectly accurate. It is an approximation but they are very, very fast.

And so what data types should you use? Who knows? What is your data? What are your needs? Do you need it to be fast and accurate and whole numbers? That's obvious, pretty close, but super fast, floating point. Fractional and perfectly precise forever even if it's a little bit slow, numeric.

We're gonna look at numeric in this video. And then, we'll move on to floating point. Let's look at an example. We've got an example right here that is again just super basic description in interest rate. Now when you're dealing with interest rates, you probably don't want to use an integer because you wanna represent some sort of fraction of a percent, right? Instead of going all the way to floating point, because we must retain accuracy when we're dealing with financial data, we're gonna stop off at the middle and use numeric. We could, in fact, use decimal and if we ran that, we would see that interest rate is numeric. So it's the same thing.

Why do I continue to show you this? I could just tell you that decimal and numeric are the same. And it would be true. I think I'm good at explaining things. I hope you agree. But what is more helpful to you in your career in the long term is seeing how I can prove things. And hopefully, you can take some of those things away and say, "All right, I don't know the answer, but I can use a few different methods to prove something to be true." Reading the docs is great. Reading books is great. Proving something to yourself, honestly, that's better. So throughout the course I will continue to try to show you how I can prove things and hopefully you can take one or two of those things away and prove things for yourself. Let's carry on.

I like the word numeric better. That's just me. You can pass through a few arguments here. So as it exists right now, this numeric can hold anything. In fact, it can hold bigger ranges than bigint. And so I'm gonna grab a massive number right here. And so if I were to do select this massive number and then cast it as a bigint, we will talk about casting in a little bit. So don't worry about that. But what I'm basically saying is take this literal value and cast it to a bigint. And if we were to run that, you would see bigint out of range. If we maybe lop off one, two, we gotta lop off a lot, don't we? Let's lop off a lot, there you go. So now it fits within the bounds of a bigint, which is your eight-byte integer. But when it's this big, it doesn't. However, if it is numeric, you're good, you're totally fine. And you still have the ability to have decimal places. And so this without passing through any arguments is an unbounded, perfectly precise representation of a fractional number or a whole number that could be even bigger than bigint.

Now the thing that I was going to tell you about before we did that is you can also pass through, you can pass through some arguments. And so the first argument that you're gonna pass through is called the precision. Don't type the word precision. We're gonna put a number there.

The second argument that you're gonna pass through is called the scale. So if we look at a number, let's say that we're looking at a number 12.345. The entire number of digits here is five. There are two on the left side of the decimal and three on the right side of the decimal. The number of digits is also called the precision. So the entire number of digits is the precision. Now the second argument or the second parameter that you can pass is the scale. And that tells you how many digits can go on the right side of the decimal place. So if we have 12.345 and we were to take that and say 12.345 and for precision, we want 5. And for scale, we want 3, whew! Glad that worked. We didn't lose any data, right? We didn't lose any precision whatsoever.

Now, if we were to put 123 like that, you'll see, shoot, we're outta range. We don't have that many digits. We're not allowed that many digits. And if you run that, you'll see we've got our mix, our mix is a little bit wrong, right? We've got two on the right side, three on the left side, but that doesn't work. So let's come back out here, 12.345. Now what happens if you say, in fact, I just want 2 on the right side of the decimal place. My fractional should only be two decimals, it gets rounded. So what happened here was we inserted, we tried to insert this or basically just casting, but imagine you're inserting it. You tried to insert this, it said, hmm, I am allowed five digits, but I am only allowed two digits on the right side of the decimal place. So that's a five. So I'm gonna round this guy up to a five, and then we're gonna drop that guy off. And now you'll see that is exactly what we ended up with.

What we have learned here is that numeric with no parameters except anything and everything and it retains it all. That's great, that's kinda like a text column. Numeric with parameters enforces some sort of range in terms of the number of digits and the number that can be on the decimal side. That's very similar to a character varying column, right?

You can say you can put as many characters as you want in here up to 2 55. And so you can do that. That's very similar to numeric passing through the precision and passing through the scale. A few more interesting things about the parameters that you pass in, you don't have to pass a second one, it's just gonna set it to 0. So you don't have to pass the second one. It's gonna set it to 0 if you want. If you find this to be more clear, I would do that. I have no problems with that. You can also pass negative. So if you pass negative, something wonky happens. And so let's add some more.

Let's do 1234567. And if we look at that, you'll see it has now rounded from the decimal place two places in it rounded that. And so instead of saying here's, you know, the number of digits you can have on the right side when you go negative, I think this is since PostgreSQL 15, so you might not have this depending on your version. But when you go negative, it says, all right, I'm gonna work in from the decimal and round those down. Now this is weird, right? Because we said you can have a total of five digits round from the decimal two places in which it did, it faithfully did that. But boy, that looks like seven digits to me, doesn't it? 1-2-3-4-6-0-0, unless I'm doing something wrong, that's seven digits.

Here's the important thing that we need to know about this. It represents, this five represents the significant unrounded digits. So in fact, if we were to add one more here, it would say nope, can't do that. Or if we were to change this to -1, it would say nope, can't do that. But because with the rounding in the number of digits that we've passed here, we're still at five. We're still at five significant digits.

I don't think the zeros actually get stored in fact. And so it's not like a padded character column or anything like that. And so this is useful. I suppose, yes, that could totally be useful to round to tens or hundreds or thousands on the way in and store it that way. It exists and I think it's edifying for you to know that it exists. I think the thing that you will see most commonly is something more like that, where you say total digits and digits to the right of the decimal place.

Numeric, same as decimal, numeric and decimal are the same thing. Numeric can hold values bigger than a big integer. It can hold exactly precise values and do exact precise math on it. It can hold fractional values. It is quite slow. So if you need exactness when it comes to fractions, use numeric. Otherwise, let's talk about floating point.