Altering a Table & Postgres Data Types

[00:00:00]
>> Let's create a table again, I'm just gonna copy and paste it this time. And let's now change a table. Sometimes we'll have to do this, right? So I'm gonna say ALTER TABLE ingredients, and we're gonna add a column. Okay, and then we're gonna add a column of an image, right, cuz we're gonna want all our ingredients to have some representative image.

[00:00:31]
So I'm gonna say ADD COLUMN image. I'm just gonna make another VARCHAR of 255, cuz for the most part, the URLs are not longer than 255 characters. In our case, they definitely will be. So ALTER TABLE, you give it the table that you wanna change. You tell it what you wanna do.

[00:00:50]
You can also drop columns, you can change columns, right, from one data type to another. These are all things you can do with an ALTER TABLE statement Okay, so now, if I say recipeguru, Not recipeguru, it's ingredients. You can see here it now has an image. And by the same token, we can ALTER TABLE ingredients DROP COLUMN image.

[00:01:32]
And now, if we do that again, you can see here we had an image up here, and we no longer have an image down here. Yeah.
>> If you had kept the table with the data in it and tried to add a not null column, could you do that or you run into errors?

[00:01:51]
>> So the question is, if I have a not null constraint, and we are gonna talk about constraints. You can see here this says nullable, not null, and if I try and add something that has a not null, what happens? You have to tell it what to do.

[00:02:07]
So I actually don't have anything like that in here, but if you just try to do it and didn't do anything, it would error, right? Because it's like you're trying to do something, these all don't have anything to do, so in general, you're gonna give us some sort of default value.

[00:02:21]
Or you can give us specifically like, okay, for all of these columns, give this one this one, this one this, this one this, but you have to provide for that kind of thing. The easiest thing to just say is, give all of them empty string or something like that and then go back and change it.

[00:02:33]
>> Can we add a column that stores image content?
>> So I believe the question is, is can I read out the binary data, actually dump the actual image, not just a path to the image, but the actual image, and then store that in Postgres, you absolutely can.

[00:02:48]
I'm gonna say that 99.999% of the time, that is a horrible idea and you should not do that. But it is possible, there is binary data type that will store something like that. Postgres is made for fast querying and retrieving of information, and you are not going to query by binary data, so storing it there doesn't make a ton of sense.

[00:03:10]
When there's something like, I don't know, Cloudflare or something like that, that's much better at storing images and handling images. But if it really is, you're just putting some stuff in there, because later, you wanna get them out, sure, it does technically fit there. So let's go back and look at where we were.

[00:03:33]
We dropped image, which we did that. Just like we did above, you can add constraints like unique or not null. Unique will make it so that, for example, let's say we wanted to make sure that no two ingredients had the same image, right? I don't know if that's a constraint you have, you might have it.

[00:03:50]
If you did, you'd say Add Column image VARCHAR unique, right, and that will put a unique constraint on which make sure every insert is different from another one. You could also put not null. In fact, we're gonna do that here in just a second with this one right here.

[00:04:07]
But this is saying that it cannot be empty, you always have to provide for it. Even if it's an empty string, you do have to provide for it. So let's go ahead and do that. We're going to ALTER TABLE ingredients. So notice that I didn't put the semicolon here, which means it's going to think on the next line, so, okay, you're continuing your query here.

[00:04:49]
ADD COLUMN image VACHAR (255), ADD COLUMN type VARCHAR (50). I'm making this one only 50 long, because I know these types are never very long, okay. This one, we're gonna say that it's not null, so that everything has to have a type. And I have ALTER TABLE ingredients ADD COLUMN image.

[00:05:14]
I forgot a comma after the image here. So this one right here, you do have to, because I'm doing multiple ad columns, I have to put a comma there. So it's usually pretty smart about telling you like, hey, this is where the parser of SQL lost your train of thought.

[00:05:37]
And this particular one is like, I was not expecting to see Add COLUMN type here, which means it came right before it. Okay, and now if I say describe ingredients, you can see here there is now a type, an image, a title, and an ID. These ones are all not nullable.

[00:05:59]
Whereas the image is nullable, we don't require it to have an image. Yeah, Dustin.
>> Are table names case sensitive?
>> Table names, I believe, are case sensitive. Honestly, good question. FROM, No, it looks like they're also case insensitive. Today I learned, I've never tested that theory before, and I'm inviting you to not test that theory either.

[00:06:33]
There are so many data types in Postgres that I just don't even wanna get started with how many there are. But if you wanna look through them, these are all of data types that you can have in Postgres. So many data types. And honestly, this is not even it, because you can actually add extensions to Postgres to add things like geolocation data, right?

[00:07:03]
PostGIS, which will allow you to say how far is this point in Seattle from this point in Minnesota, right? And you can do those kinds of calculations using Postgres. That's an extension that you have to add to Postgres to get that. Today, we're gonna be going over probably ten different data types, but just know that there's a lot more.

[00:07:25]
So one of the things I wanted to show you here with this ALTER TABLE, you don't have to do the add columns or drop columns or anything like that one by one. You can do five different operations. I could have these two, and I could also drop another all in one ALTER TABLE command, and that would allow me to do that all in one query.

[00:07:43]
The reason why that's helpful, if you might imagine, imagine you're trying to modify something that's out in production, right? If it fails, you want it to fail all at once, right? You don't want three of them to succeed and one to fail, you want everything to fail, so that you can go back and try again, so that you have one holistic attempt.

[00:08:00]
This is called it's transactional, right, either it worked or it didn't work, it doesn't impartially work. So that's the advantage here of doing multiple things on one ALTER TABLE. Yeah, Mark.
>> Me and a few other people in chat are getting the error column type of relation ingredient contains null values.

[00:08:23]
>> Yeah, so this is the question that we were asked earlier, what happens if you try and add a column? I imagine most of you probably didn't drop your table, right? If you didn't drop your table, that means you have something in there, right, which will be a bell pepper.

[00:08:40]
So basically here, we're trying to add a column of the type that is not null, but there's something there that is null, right? Because if you add a column, that means it's going to add something and it's not going to have the ability to deal with that.
>> We started from scratch.

[00:08:57]
>> That's gonna be easier, yeah. That's probably what I would do, DROP TABLE ingredients, And then you can just CREATE TABLE ingredients.
>> Yeah, someone just added the words default and then in quotes vegetable and it was able to-
>> Okay.
>> Do that.
>> I thought it would be something really simple like that.

[00:09:23]
Here's my thing with SQL. Obviously, I did a lot of work preparing for this class and getting all of this queries correct. I never remember this all off the top of my head, absolutely never. I Google constantly when I'm doing SQL, because it's very specific in terms of like what syntax comes where, what quotes go where, what double quotes or single quotes.

[00:09:43]
In fact, I imagine someone right now is probably having a problem of putting double quotes around something. You can't do that, you have to use single quotes when you're talking about values. We'll talk about that in a second, but if you're doing default, quote, vegetable, use single quotes, not double quotes.

[00:10:00]
Let's just go ahead and see it, cuz I think that might be a useful thing for people to see. Create that table. Come back here ALTER TABLE and then DEFAULT vegetable, and there you go. And I'm saying these here have to be single quotes, specifically.