As we're on our little detour from these strict column types, I wanna show you a few more, just a little grab bag of things that we've been doing a few new things, I wanna explain all of them fully. We've been doing it like this. Select 100:: money, that is the Postgres specific implementation of the SQL generic cast.

If we did cast 100 as money, we would get the same thing in both cases. How do we know it's the same thing? Well, it looks like the same thing. That's not good enough. If we were to do this int8 int4, that looks like that, that woof, man, those look like the same thing. Let's run them both at the same time. Boy, that is really hard to tell apart. That is where the pg_typeof can come into play. This is a function that will tell you what the Postgres type is. That one is a big int as we would expect, that one is an integer.

If we were to change this to that, now we know we're working with the exact same data types. This pg_typeof can be quite useful if you're trying to do a little bit of debugging or figure out what is the output of a particular function. That can be quite useful. If you need portability, you should use the cast. If you're just strictly using Postgres, you can use this double colon.

The idea of portability to the int of switching your database does not move me. I personally don't think you should nerf your implementation because you might change database flavors in the future. I don't know how often that has happened. It's not zero, it's definitely not very common. The idea of portability moves me if you're writing some sort of library or framework or something that could be used by other people in multiple different flavors of database, then I would say stick as closely to the SQL standard as possible or write drivers that leverage the power of each individual database.

If you hear portability, you gotta use this for portability. I don't know, take it with a grain of salt. I don't think you should change your database halfway through a project, of course that does happen. There's one other thing I wanna show you. Select let's do integer 100. If we select that we get a, what appears to be an int4 because that's what the column was named, typeof is not real. I think that's JavaScript, pg_typeof gives you an integer. This is called a decorated literal. This is the literal, this is the decoration. It is similar to cast, unfortunately not exactly the same. Here we have a literal that is 100 we're trying to decorate it as an integer or an int8. It doesn't work. If you do see this out in the wild, it is legit. It's called a decorated literal. There's not a lot written about it, not a lot of people talk about it, it does exist. This gives Postgres the hint that this 100 is actually an integer.

I like for clarity, I like sticking to casts because we're living in a Postgres world, I like to use the Postgres flavor, that is just my opinion. There's one other thing I wanna show you while we're talking about cool functions. This is cool, right? I'm cool, right? If we do pg_column_size, well we gotta pass it something, pg_column_size is going to tell you how many bites a certain thing occupies. Let's do this. Let's do 100 as int2. What do you think? It's all kind of obvious, right? Two. There you go. You're smart. Good job. Okay, int2 for int4, let's do that. Nothing here will surprise you. This is however a good example of pick the smallest column type that holds your data because if you store the value 100 in these different column sizes, they occupy much more space than if you were to store it in a smaller one. Even though the value is quite small, the size is determined by the column type.

That is true for integers it is not true if we were to look at numeric. If we come down here we look at numeric, well, hmm, that is eight. What if we made it 10,000 points a lot? Well then, well that's 14 what if we made it point a lot more you know, a lot bigger. It's just gonna continue to grow. Back in the numeric section, we talked a little bit about how numeric is a varying size data type, much like a text or a varying, character varying of our car column.

Numeric is the same way in that it will allow basically anything in there retain it. The size does change however. Hopefully that helps. Hopefully that gives you a few more tools in your tool belt. You can cast things into the desired type. You can figure out what type they are, or you can figure out how much space does this occupy. If you ever need to do any debugging or planning upfront on designing your schemas, those things can help you.