We just covered integers. Now we're gonna look at fractional numbers. I think it might be helpful to think about this kind of on a spectrum so we can compare and contrast. 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. 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.

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. 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. When you're dealing with interest rates, you probably don't want to use an integer because you want to 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. 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. 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. 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. 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. As it exists right now, this numeric can hold anything. In fact, it can hold bigger ranges than bigint. I'm gonna grab a massive number right here. 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. Don't worry about that. What I'm basically saying is take this literal value and cast it to a bigint. 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. Now it fits within the bounds of a bigint, which is your eight-byte integer. When it's this big, it doesn't. However, if it is numeric, you're good, you're totally fine. You still have the ability to have decimal places. 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.

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. 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. 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. The entire number of digits is the precision. Now the second argument or the second parameter that you can pass is the scale. That tells you how many digits can go on the right side of the decimal place. If we have 12.345 and we were to take that and say 12.345 and for precision, we want 5. 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. 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. 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. 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. That's a five. I'm gonna round this guy up to a five, and then we're gonna drop that guy off. 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. 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. 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. If you pass negative, something wonky happens. Let's add some more.

Let's do 1234567. If we look at that, you'll see it has now rounded from the decimal place two places in it rounded that. 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. 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. 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. It's not like a padded character column or anything like that. 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. If you need exactness when it comes to fractions, use numeric. Otherwise, let's talk about floating point.