Creating & Populating a Table

[00:00:00]
>> So let's create our first table. We're gonna be creating an ingredients table. So you can see here, let's just run it and then I'll we'll talk about it. So you can just copy this or we can even just type it out a little bit so you can actually kinda get a feel of what it feels like to type it, CREATE TABLE, ingredients, and it's gonna have a couple of Things in here.

[00:00:34]
So we're gonna have an id which is gonna be an INTEGER or you can put INTEGER like this. Or you can just put INT, it's the same thing, or INT4. I don't know why that would ever be the one that you would choose, but you can choose that.

[00:00:48]
INT it is a PRIMARY KEY It's GENERATED ALWAYS AS IDENTITY. Okay, then we're gonna have a title it's gonna be a VARCHAR and we're gonna give it a length of 255 it is UNIQUE NOT NULL, okay? Then we're gonna close our parentheses and we're gonna hit a semicolon.

[00:01:24]
So first of all the way that we know that we it successfully ran as you see here it says CREATE TABLE it's going to repeat back to what you just did to tell you this worked right? And now if I hit \ d, you can see here I have a table and a sequence which is what I'm expecting.

[00:01:40]
And if I say \ d on ingredients, they will actually describe the table to me which is an integer, id ,and a title. So all this works let's actually break it down piece by piece of what we actually just did. So CREATE TABLE, we're telling SQL we would like you to create a new table, you can think of a table is basically just a spreadsheet, right?

[00:02:04]
It's got rows and it's got columns. The columns are all representative here like id and title, right? It's going to have an id for every single one of these, which is basically just a unique identifier, right? That's literally what it is. And then we're gonna have a title which is gonna be something like cucumber or flour or chocolate or whatever you wanna think of there, okay?

[00:02:27]
We're naming the table ingredients, and then we're going to give it all of the various different columns, right? You can basically think of like, again, setting all the column names in your spreadsheet. You have to tell it what type of thing it is. We're telling it the type of thing, the data type here is INTEGER.

[00:02:41]
So this can only be a number if I try and put a. Apple into the id, it's going to say this is not an integer, you can't do that. So it actually is going to verify and make sure that that's true. We're gonna say this is the PRIMARY KEY, every table that you're going to create is going to have some sort of primary key, right?

[00:03:00]
It's gonna say like, this is how I'm going to index this table right? So in this case, we're just saying that the id is the PRIMARY KEY. That's going to be very common. It's not always true. I'll show you when later in the course it's not. But for now, most of these are gonna be ids kind of like this.

[00:03:20]
And then this GENERATED ALWAYS AS IDENTITY. If you've done previous versions of In postgres this to call this serial, S-E-R-I-A-L right, not the food. I think you actually still can do I think it still works but it's good then if it's not been deprecated it's at least been de emphasized.

[00:03:43]
This is actually more compliant to the SQL spec that's why this is preferred now even though serial is a bit more simple right? But here we're saying basically we want you to always generate this key for me and I want it to be incrementing, right? So the first thing I'm gonna create is gonna be id one, the second thing I created me id two.

[00:04:02]
If I delete that one and I recreated the next symbol that created will probably be id three, right? So it's going to give everything across your entire app, a unique identifying key that we don't have to keep track of, that's what this ingredients_id_seq, this thing right here. That's what this actually keeps track of right here, it's the next thing in the sequence, right?

[00:04:25]
The next number that's going to be put into that id. Okay, so that is the first column the second column we're saying that we're creating a title right? The VARCHAR is a type of string right? So text right? A free text and we're saying that this has a max length of 255, 255 characters, right?

[00:04:52]
So if you have a ingredient that's 256 characters, it's going to truncate the end of it and it's not going to store that last one. We could put another one here called text. So it would just be title text UNIQUE NOT NULL, right? Text is infinite length, right?

[00:05:10]
It's variable length but titles in general, like the title of your ingredients not gonna be very long. This allows postgres to kind of shrink that storage size a little bit, right, keep it a bit smaller. And then the length is 255. That's a common one to pick just because that'll fit in some amount of bytes.

[00:05:31]
I can't remember exactly how many but. It's pretty reasonable that we can expect the titles or ingredients to be under 255 characters in length and that is our first table, it's an empty table but nonetheless it is a table. So we've created our spreadsheet and we've put nothing into it but we now have a place to put data.

[00:05:54]
You're gonna hear me a lot today use a spreadsheet analogy it's because database, well, basically a spreadsheet is a database. It's just a database that it's really easy to edit with Google Sheets or Excel, right? And it's not actually theoretical. You can actually use Google Sheets as a real database.

[00:06:13]
You can write SQL queries that work against spreadsheets. So it's not theoretical, it's actually a true thing that you can do. I'm not gonna recommend that you run your entire company on Google Sheets, though I've heard of people doing it. So we're gonna talk about inserts more in the next chapter but I want you to just copy and paste this bad boy with me the INSERT INTO ingredients here mostly so that we can see what happened.

[00:06:42]
So I inserted into ingredients and inserted a bell pepper so now if I say SELECT * FROM ingredients you can see there I have one thing in my database I have a bell pepper. But notice here at nowhere did I specify the id, right? The Id just came with it.

[00:07:03]
This is what I was saying about this PRIMARY KEY GENERATED ALWAYS AS IDENTITY. That's where that comes from, right? So it's going to just keep updating that so you don't have to keep track of it. The database keeps track that for you. Again we'll talk about inserts in depth in the next chapter I just wanted you to as well select some the chapter after that.

[00:07:26]
But I wanted you to see this is how stuff makes it into the table and now we actually have something in our table. Yep, so they used to call it SERIAL like that. But now that generated all these identity is more compliant to the generic SQLs one, and they're trying to, like, be good citizens of the SQL world.

[00:07:42]
So that's where GENERATED AS ALWAYS comes from and so you should see something like that which you saw. So I wanna show you how to drop a table so let's do that just for fun DROP TABLE ingredients. And now if I say \ d you'll notice that it did not find any relations that's what drop table does.

[00:08:11]
By the same token you can say DROP DATABASE recipeguru. We could totally do that I'm not well whatever let's just do it it's fun except you don't have to do this but. And then now we can drop that's a DATABASE recipeguru and l and behold it is gone.

[00:08:37]
So CREATE DATABASE recipeguru. Cool, a note on semicolons, a semicolon means the end of a statement. Just like in JavaScript where you put a semicolon at the end of a line to let JavaScript know period, full stop, end of sentence, it's the same thing. In postgres, it is required here though.

[00:09:07]
There's no automatic comma insertion in postgres. It's going to keep going until you give it a semicolon to let it know that I'm finished with this. So it does mean that you can put multiple statements on one line, right? Multiple queries, I'm gonna use those terms more or less interchangeably, statements and queries.

[00:09:28]
The one thing you should notice here when I do this \c recipeguru, I am not putting a semicolon there. It's because PSQL doesn't care, right? This is not SQL, this is PSQL helper things, right? So it doesn't care about semi colons but your SQL does, that's why sometimes you'll see it and sometimes you won't.