Creating a Database, Table, and Record

[00:00:00]
>> Let's create our first table. So, like I said, we're gonna be doing a message board today with Postgres. I kind of tried to choose like a good problem for each one of these. So they're not exactly they gonna be the same problem. Whereas like a pet adoption app, is good for Mongo, something that's more relational like a message board is really good for a relational database.

[00:00:22]
So we're gonna say create table. Now something I'm gonna tell you upfront. It's optional to capitalize all this stuff. So you can totally say create table like this. It'll get it for your benefits. And in general people tend to capitalize the commands, they can see the difference very quickly between what they're imputing and what is like a official SQL kind of thing.

[00:00:48]
If you're feeling lazy like I often do when I'm writing SQL, feel free to just put everything in lowercase just know that that's why I'm capitalizing. It's hopefully for your benefit. So CREATE TABLE users, Open parentheses. We're gonna create a table here. And we're gonna have a user id.

[00:01:09]
This is gonna be, you can put this as INT or integer, and we'll put into Gerges for this one. We're gonna make this a primary key. So primary key means like this is the thing that the database is primarily going to indexes on so MongoDB had an underscore id, this is that but for this.

[00:01:35]
And then we're gonna have generated always as identity. This is going to be an arrow incrementing id. So the first record that we're going to insert into this database, or table rather, is gonna have id one, then we'll have id2, then id3, then id4. And it will keep track of that for us.

[00:01:58]
We don't have to keep track of it. That's what this generated always as identity means that's what it's for. In previous versions of Postgres, this was called a serial. If you're familiar with Postgres, you might have seen that before. It's not exactly the same thing but it's really close.

[00:02:17]
The reason why they switched to generated always as identity. This is actually part of the official SQL spec. So there is a an SQL spec that all of these SQL Server, and Postgres, and MySQL share, and for the most part, they're relatively compatible. But serial was a Postgres thing, and so they wanted to come back to the the official SQL way of doing it.

[00:02:39]
And so that's why they came into generated always as identity. So for example, I think this create tables statement that we're about to issue, I think it would work as is with MySQL, for example. Not all of them will, but some of them will. User id, okay, then we're gonna have a user name.

[00:02:56]
We have to tell what kind of thing it is. Whereas this was an integer, right, which is gonna be a number 1234, right? The username we want it to be a string of characters, right? So in SQL, you call that a VARCHAR or a variable amount of characters, or a string.

[00:03:14]
So this varchar we're gonna give it a length, we're gonna say usernames can only be 25 long, that's what this means. It's going to be unique, so no two people can have the same username, I think that makes sense. And we're gonna say this is never null, which means if you're going to insert into this database, you must include a username, or I will not accept this query.

[00:03:40]
Okay, same thing with email, varchar. We'll make this 50 I don't know if that's a good length for emails. It's probably too short but that's what we're doing for now. Unique, not null. Okay, full name for Varchar 100 not null. I don't know if full name 100 that's probably not long enough either like the king of Thailand couldn't put his name in there.

[00:04:13]
If you've never looked at the king of Thailand name look at it a lot. [LAUGH] last login we'll make this a timestamp. So what we're gonna do here is every time that the user logs in, we're going to log that they've logged in with this last login. And this is useful for if someone doesn't log in for like six months, you can just say, all right, clear out all of the people that have been logged in six months.

[00:04:43]
This one can be null, right? So if someone creates an account and never logs in, this could be null, right? So we're not gonna put NOT NULL. And then we're gonna do a created_on, of when they created this account. This would be a timestamp. That's not null. Okay.

[00:05:06]
And there you go. We now have created a table. If we put slash D you can see here, we have a user's table as this first one represents. And then the second one, it creates this little sequence type that it uses that to keep track of where we are in that auto incrementing id.

[00:05:22]
So all of those auto incrementing id's will have their own little sequence in this list of relations thing. So something you can do instead of using a bar chart, you can use something called text, which is a different kind of data type. That basically says that we're not putting an upper limit on it.

[00:05:38]
It can be like 65,000 characters ish. I think 65,000 is the right one. But with varchar we can just say like there's a hard cap at this that we don't want people to have 65,000 character emails. There's a lot of types of Postgres data. There, I dropped the link there into the course notes of all the various different kinds of data types for Postgres.

[00:06:08]
Feel free to check that out if you're interested in seeing what other types are available. Or there will be probably a course done on that as well. All right, so now we have a table. Tables empty, so let's go ahead and insert something into that table. So next thing we're gonna do is we're gonna do insert into this is going to allow us to do an insert into the database.

[00:06:34]
So we're going to insert into users. That's the name of our table. And we're going to insert a username. So we have to actually type out the name like of the columns or the fields that we're gonna be putting in. Email, full name and created_on like that, and then we're going to say values.

[00:06:56]
And this is gonna go in that same order. So the username we're gonna say is btholt By the way, you must use single quotes here do not use double quotes in SQL or SQL you have to use single quotes. Some emails lol@example.com, name is Brian Holt and then this is gonna be the created on and so we wanna created on to be right now, right cuz I'm creating my account right now.

[00:07:34]
So I'm gonna say now like that that's a function built into Postgres. That's gonna give me the current timestamp back. Okay, I'm gonna close this parentheses right here, put a semicolon at the end. And you can see here I have an insert 01. This one represents that I successfully inserted something into my database.

[00:07:58]
The 0 represents the oid. Which we're not covering nor using throughout the span of this course. So no worries about it just ,yeah. It's not something we're going to cover. It's not important. All right. So now we've inserted something into our database that is like at the base level of how you insert things into SQL.

[00:08:24]
This language here this CREATE TABLE this insert into. This is what SQL looks like it almost reads like English sometimes. So, which is nice. I like SQL for that reason ends up being fairly readable. So now I wanna get it out I wanna read from the database. How would I do that?

[00:08:41]
Well, the most basic easy way to do that is to say select star which is gonna mean everything right select everything from users like that. And you can see here, this will read back everything that I inserted into users. So select is basically saying get this stuff out of the database stars keep saying, I don't wanna do any projection, right?

[00:09:06]
This is where you would do the projection part. We talked about that in MongoDB. If I wanted to do that, I would just say select username, and maybe full name. Like that and this would only select username and full name it would leave everything else behind. So the star is a wild card means select everything from it.

[00:09:26]
And use just say from which table from users. Notice here on last login, it's null, right? Cuz we didn't provide it. So it just says, okay, you didn't give it to me. That's gotta be null. So this user, btholt, has never logged into our system. So it's good question here.

[00:09:44]
Is there a default length or do we always have to specify length for each field? With varchar, you do have to specify how long the string is, or how long the string could potentially be. Again, use that textfield. That's kind of how you're saying I give up there's no upper bounds on this.

[00:10:04]
use as much space is necessary. There's some other subtle differences between varchar and text that probably outside the scope of this class, but it has to do with like how much inquiry from them