Cast Function in SQL

All right, so now let's talk about data types. When we have our built-in data types, we see things like numbers, serial numbers, integers, numeric, those are numbers. These data types are created by the person who defines the database.

So they're not something that we can change ourselves, not something even that we'd want to change in the actual database, to change the database. But sometimes for the sake of a query, we need to change the database, not change the database, but to change the data type for a query. And so even if we could change the database, we don't want to.

We might break the website, we might break the application. When we need to change data types just for one query, we use CAST. So each column is assigned a data type, not by you.

It's in whatever the right format is for the need of the database. Numbers, text, date, date time. For an individual query, when it's not stored in the format we need, we can use CAST to convert it into the format we need.

Nothing we do in a SELECT statement will change the database. This is going to be done in a SELECT, so we're not going to change the database. We're just changing the data type for our particular query.

So let's see how this works. This particular file is not going to have any challenge questions. We're just going to do some warm-ups here, because later we're going to use this.

We just need to know how this works, and we're going to see more examples of how to use this later. I'm going to choose my database up here, because this is a new file, and I haven't told it what database to use. All right.

Now, if we want to throw something into the result set, just to say, hey, just put this number down there. If I just say, put the number two down there, it'll just throw the number two into my result set. This does not use the database at all.

It's just a way for me to show you how something works to say, hey, let's just throw the number two down into my result set. If I want to show you how CAST works without even using my database, I can say, let's take and CAST. Now, this is the first function that we're using.

Functions have parentheses after them, and in those parentheses, you're going to say, what does this function do? Functions perform a function. They perform a task. In this case, casting or converting from one data type to another.

So is 2.6, is that a decimal or an integer? An integer is a whole number. At 2.6, that's a decimal, right? So that's a decimal number. If I want to cast that as an int or integer, notice how that converts it into a whole number.

In this case, it rounds it up or down as appropriate. If I cast 2.1 as an integer, I'll get two. If I cast 2.4, I'll still get two.

2.5 or above gets rounded up to three. So this will round the number, although it's not specifically a rounding function, but it has to round the number to convert it into a whole number because whole numbers don't have decimals. Think about this not so much as the rounding function.

Think of it as it is converting one data type to another data type. A decimal data type is always going to have decimal numbers. Integers do not have the decimal parts.

That is a different kind of data. You might say, but they're just numbers. Yes, but in a database, there are different kinds of data.

This is just one example of this. Also, to kind of continue on this, let's say I take this and let's say I put quotes around that. Now, when you put quotes around something, what do those single quotes mean? String.

So that is not a number, technically speaking. That is a string. If I convert that into a number, decimal, and I'm going to explain what these numbers mean in just a moment, but let me just show you here.

Notice now how it says 2.59. I set a two-digit decimal place here. This is converting a string and notice how it says numeric and it says one, two, three. I just converted text into a number.

The most important thing here is converting one type of data into another type of data. I'm changing text into numbers because what if you want to do math on text? Doesn't work. What if you want to do math on a number, but it's stored as text? Oh, we have to convert the type of data.

Now, the decimal option here. Decimal has how many decimal places? Three decimal places, four decimal places. Let's say I just do one decimal place.

So here, 2.6. I could also say zero decimal places, although I would rather just say int for that, but I could choose how many decimal places. Yes. What does a nine mean? So in terms of storing data in a database, when we store information, we don't want to allow unlimited amounts of storage because some people will try to attack our database and if we don't put limits on our database, they might try to fill it in with a number that's so big that it fills our whole hard drive and crashes the database.

There are bad people out there. We normally always want to have limits on things. So how many total numbers are you going to have? If you look at this number, how many numbers do I have? One, two, three, four, five.

We don't count the digit as far as the numbers. So I've got five numbers. So I need, if I was going to reproduce this four decimal place number, I would need five total digits with four decimal places.

That would be the least that I need. I can always have more, right? But let's say for example, if I'm doing a one decimal place number, in 2.6, I have two numbers. I at least need this to be two because I need at least two digits.

If I make it one, I'll get an error saying you don't have enough numbers. So as long as this number is enough numbers for your number here, you'll be fine. It's okay to have more, like this could be up to nine digits.

So one, two, three, four, five, six, seven, eight, nine. I don't care where the decimal is in this. It can be anywhere, but you can't have more than nine numbers in this.

So this would be a nine numbers. And let's say it's one decimal place. So I could do something like this.

So I could do up to tens of millions. I couldn't go to hundreds of millions unless I go up to 10. And then I could have another one over here.

Does that make sense? So it's how many total do you need? You might say, well, for my query, I don't care, but yes, but we're still using the same data types that the people making the database need. So I normally just type in nine because it's a single biggest number that I can type in with just one number. And most of the time I don't need to go beyond like, even if this were two decimal places, let's say it was two decimal places that still gets me up to millions.

I normally don't need to go beyond millions for numbers. So it's okay. If your number is less, you just can't make this number too small.

It's okay. If it's bigger. So just make it big enough that you have plenty of numbers in there.

In this case, technically I only need three. So, but you don't need to make it exactly the number, just as long as you're higher, it's just not enough. As long as you have enough, you're fine.

When you have too few, you get in there. So mainly focus on text to number. That's what we're doing here.

Also, sometimes you'll see, it'll say, for example, numeric, that would also work. I just like decimal because it literally says decimal numeric would work the same way. We're skipping SQL Server.

So this is one thing where SQL Server is sometimes different from PostgreSQL. When we see something like this, I don't say anything that works in both. But if you see something about SQL Server, just ignore the SQL Server stuff.

We're not going to worry about that because sometimes SQL Server is a little different when it comes to these things. Now, when it comes to PostgreSQL, so we're going to ignore the SQL Server stuff. Not going to worry about that.

Just going to focus on the PostgreSQL side of things. I'm going to switch to a different database. Now we've never switched to a different database before.

Over here, I've got company data and here I've got game shows. So in game shows, we've only got one table, the Jeopardy table. And if I double click on it, I can look at the data.

There's show numbers, the air dates, regular Jeopardy, double Jeopardy for the round, category for a certain point value, questions here and their answers. So in this Jeopardy table, it's in a different database. So far, we've always been querying one database, the company data database.

If I want to go to this table over here, if I try to say, select all the columns from Jeopardy, notice how it's not code hinting because that table does not exist in company data. PostgreSQL only lets you connect to one database at a time. And by default, when I connected into my server, it connected into company data.

Why did it do that? Because when we defined our connection, we said to connect in by default to company data, but we said to show all the databases. So I'm not just limited to that, even though that's the default. If I ever want to switch from that default, I have to come up here, not to the server because I'm on the right server, but to the thing on the right.

When I click on that, this lets me see all of my databases and I can choose the database I want to go to. And then there's this schema inside of it. A schema is simply a grouping of tables.

When I double click on that schema, it will now connect into that game shows database. So I always have to click up here and make sure that I'm in the correct database. You choose the database on the left and then double click on the schema on the right.

So here I'm in company data. When I click on it, I can choose game shows and then double click on the schema inside of that. Now that I'm in game shows, these other queries, if they were targeting company data, would not work.

But now that I'm in game shows, I can talk about Jeopardy. And now notice Jeopardy is being coded. Normally speaking, you don't put different databases on the same file.

Normally, because a file is associated with the database, we normally create different files, like one file for Jeopardy, another file for the company data stuff. To have to switch databases in the same file is kind of annoying. And because one file is associated with one database, it's generally best to just create two different files.

One for company data, one for game shows. You can switch, but it's generally kind of annoying to do so. So here is the Jeopardy table and this show number that's here.

If I show the show number, this show number, if you look here, notice how it says A-to-Z. Show number, A-to-Z, VARCHARD. You might say, well, but that's weird because the number is not a number. Yeah, technically looking at it, it is.

Although, let's say in there that maybe show number always has to have like, maybe it's 0001 and you need preceding zeros. If you stored it as a number, you couldn't have those preceding zeros because a number format would get rid of those. So whether this was intentionally done correctly or not, it is stored as text.

So show number is text. What if you want to add one to it? So let's say I add another column for show number again. So here's show number.

Here's show number again. What if I try to add one to it? It says operator does not exist. Character varying VARCHAR plus integer, which is one.

That doesn't work. They're like, you can't add numbers to text. It doesn't work unless you cast this, which is text.

If I cast that as an integer, then it would allow me to make it numbers. And you can do math with numbers. So it starts as text, but you can say, no, I don't want it to be text.

I want it to be a number because I want to add one to it so I can do math. The main thing to understand about cast is we're converting a data type to another data type. So text to number, for example, a whole number, a decimal to a whole number, something like that.

When something is not in the data type you want, you convert it to what you want. So let's all open up this 1.1 cast. There's just a couple of things to do here.

Ignore the SQL Server side of things. Just do the PostgreSQL. And there's no challenges.

So it's just a couple of things. So let's just take a couple minutes to do these. And then we'll come back.

We're going to now that we know this, we're going to see use cases in various times as we go through in future exercises that we do.