PostgreSQL CRASH COURSE - Learn PostgreSQL in 2022

so you want to learn postgres huh well i know a thing or two about postgres in fact i would say that learning postgres and sql has been one of the most beneficial skills i've learned throughout my entire career not only has it been helpful on the job it's also been a really impressive skill to be able to flex in interviews with potential employers and i've landed multiple contract jobs just because i know postgres well enough to be able to do things that other developers simply can't today in this video i'm going to give you some initial skills to help you on your journey to postgres mastery we're just going to be talking about a lot of the basics perhaps in future videos we'll dive a little bit deeper and talk about some more advanced concepts but this should lay the foundation for your journey to postgres mastery so without further ado let's dive right in so the first question is what is postgres what is sql really well postgres is a relational database you're going to hear a lot about relational and non-relational databases out there and an initial assumption a lot of developers might have and in fact when i used to teach i would ask students a lot what's your assumption what do you think relational means versus non-relational and oftentimes they would say well i would just assume that a relational database allows you to relate data together and while that's true you can do that in a relational database what's also true is that you can do that in a non-relational database what it actually is talking about is the math under the hood what makes the database tick in relational databases it's leveraging relational algebra and that's the reason for the name so that's just a common uh misconception that i wanted to clear up before we do uh right into postgres so sql stands for structured query language it's a language that allows us to communicate with our database and make changes um by using a series of keywords we're gonna dive into it a little bit more but just setting the stage for for the rest of our talk today so let's go ahead and talk about the core building block for essentially all sql databases which is tables and tables are they're they're essentially containers for data they're made up of columns and rows you can think much like an excel spreadsheet or a google um sheet spreadsheet you're gonna have a series of columns and then you're gonna have multiple rows often times we'll talk about entries into a um into a table as entities so if you ever hear me talk about an entity i'm just talking about one object in the database so for example here what we what i would say is maybe this first user here he's an entity in the database and what we can see is this is actually a screenshot from the editor that will be in soon is we have a series of these columns here so we've got an id column email column name column age column and password column and what you'll also see is that we have these rows we have these entries into that table and those rows have specific uh entries for each column so for example for the email column we're going to see troy at fake email.com is this user's email address and then you know same for name same for age and password here these are actually hash you're never really going to store raw text passwords in a database that's a huge concern security concern we're not going to talk about that much in this video maybe that's a great topic for future ones but i just wanted to explain why you're seeing that nonsense in the password column okay so let's jump over to an editor now and let's create our first table together alright so welcome to post to go the postico is just an interface for interacting with postgres i'll have some links for setting up postgres on your local machine as well as postago down below in the description but all you need to know for now is it's an editor that allows us to write sql and interact with our database it also gives us a nice ui so that we can see things it's fantastic for beginners so let's jump right in so here this is where we're going to write our sql so you know we can type away as normal but um sql like i said is made up of a series of keywords so what do i say when i mean keyword well a keyword is just a word that tells sql to do a very specific operation and in this example what we want to do is tell sql or tell postgres i should say to create a table okay so what table are we creating today well i think it's best to go through anything new that we're learning with a actual realistic example so we're going to build a little bit of a schema here that would represent an application that allows us to have users and posts eventually and when i say schema all a schema is is it's just the database itself the way that data is laid out is the schema okay fantastic so let's jump into it first we're going to create a table i'm going to call the table profile for now i'm going to revisit that but we'll come back to it and then the syntax for this is after we say create table profile we open up these parentheses here and then inside of here each line we're going to define a new column for that table troy from the future here i'm sorry for the interruption i just want to note that sql statements need to end in a semicolon i didn't specifically say that which is my bad but you'll notice that every statement that i write throughout this entire video ends in a semicolon it's how we tell sql that the statement is done so we always whenever you're working with a database you need to make sure that you have an id column that column is going to be what we call a serial column and we're going to go ahead and also tell it that this is the primary key the user might also have a name the name we're going to say is varchar 255 and don't worry i'm going to come back and talk through all of this what does this stuff mean you might not know but i think it's helpful to write it all out first so that you can see it together we're going to have an email that's also going to be varchar255 we're going to have the password i'm going to make the password text and then finally we're going to have an age which is an integer okay great so what does all of this mean and why are there these different uh capitalizations for certain words and some not okay so first things first let's address capitalization in programming languages you're going to have a series of conventions oftentimes in languages like javascript you're going to see a lot of camel cases in python you're going to see a lot of snake casing in sql the convention is that you uppercase all keywords and anything that relates to your table itself should be lowercase snake case so all lowercase characters snake case the reason that is is sql does not care about casing so this is the same thing it's going to do the same operation me lowercasing create had no effect on it but you write code for other people and it's important to structure things in a way that makes it easy to read so here by uppercasing everything that's a keyword it becomes very very clear what is baked into postgres itself and what is something that i'm naming what is something that i have control over so here this this profile name this is a name i'm giving it could be anything i want virtually i could call this person i could call this user but for now we we may call it profile i want to come back to that user point but that's the idea is that you're uppercasing all of the keywords and anything that is lowercase is something you're naming yourself so let's follow that to the next line and in fact i'm going to skip over id for a second because it's the most complex column we have we're going to talk about name next so name here that's well the name of the column i'm creating a column and i'm calling it name and this is something to look out for the entirety of this video is anytime you see something that's lowercase you should assume that it's something that i'm naming it's not something that's built into postgres itself next we have this which is a data type the data type is what constraints we're placing on this column itself and for me this is what makes using relational databases so powerful you can add constraints to your columns data does not just exist in containers that have rules that are made up they are enforced by the database itself i could not insert just a raw number into the name column if i wanted to and the reason is i'm telling postgres here that this column is varchar255 what is varchar that's a great question you might ask this is the syntax for a comment so i'm just you know i can write anything in here it doesn't matter the editor ignores it i'm just commenting on the column and it's good for taking notes so what i'm going to say here is varchar and i'll uppercase it is a text column that has a constraint on size okay and i know it's it's overflowing a little bit to the next line but this is the same line so the idea here is that varchar is a text based column but critically it has a constraint on how long that text can be and the upper bounds for that is 255 characters right so the max we can do is varchar255 but varchar 50 is valid as well and i think it's probably fine for name right it's pretty rare to have a name that's greater than 50 characters maybe for covering all of our bases we'll do a 100 character name column so what this means is that this column is only going to take up enough space to show 100 characters and it's a great way to optimize your database so that you're not consuming more data than you need so here again we're saying that this name column is a text column and it can only max out at 100 characters for email it's the same thing i'll leave 255 you can have some long emails we'll leave 255 and then password is just plain text and the reason you would do that is that at least for applications i've worked on in pretty much any app that you're ever going to touch actually you're going to be hashing passwords in the database and password hashes can get pretty long depending on what hashing um uh library you're using and what um hashing algorithm you're using you know whether you're using sha or or md5 anyways that's not important for this talk the point is i'm making password text because placing constraints on something that's going to be hashed it's kind of silly next up we have age which is an integer an integer um is you know well it's straightforward it's a number itself so you're gonna see you know uh numbers in here like 20 etc um i want to note that there's a lot of different types for your columns and if you want an exhausted list of them you can google postgres column types and you'll get an extremely large list of all the different types one of those such types is serial okay cereal is a special type because it sets up some additional stuff for us some additional stuff that i'll dive into more in a future advanced postgres tutorial but for now the way i want you to think of this column is this column is an integer that automatically automatically adds one for us every new entity so every single time we insert into this table it will automatically add one to the id call and what i mean by that is our first profile that we insert the idea will be one it starts at one with a serial column then the next one we don't have to do anything it'll just automatically get the id of two the id column is something you never want to edit yourself you want to let the database handle this when you're inserting profiles later and we'll talk about this a bit more when we're actually inserting data momentarily but we want to avoid touching this column this column is something we want the database to control the next part is the primary key so primary key is just telling the database that this is the key that we're using to represent each row in the table and critically um that also means that it's going to be unique right we shouldn't have multiple profiles that have the same id it's a unique identifier that we can then use later on to reference for specific rows it's incredibly useful and we'll show that more as we get later into the crash course so again serial is a data type it's an integer that automatically does plus one every time so without further ado let's go ahead and execute this statement so it worked that's what this create table is and i can show you it worked because postago is awesome and if we come back here to the lecture db and we hit this refresh icon at the top you'll notice that there's this profile table here and if we click into it we're seeing id name email password and age sorry if that's a bit small i tried to increase the font up there but but i couldn't but that's our table it's there it does exist but you might be saying troy in it seems kind of weird to call it a profile this seems like this table should probably be called user well if we run this it's gonna fail and we're getting syntax error at or near user that's a bit strange think for a minute why that might happen and you might get a bit hint a bit of a hint by the coloring here so pause the video if you want a second to just think about why that could happen all right here's why this is a keyword so creating a table that's called table wouldn't work because like i said at the start sql doesn't care about casing so when it sees this lowercase table it's treating it the same as the uppercase one and it thinks that it's a keyword right so it's seeing some syntax error it's like hey you're using a keyword here we're expecting a name there's an easy way to admit this whenever you're trying to name something in sql or reference something that is not a keyword but happens to share the same name as one you can wrap it in very specifically double quotes so we can say user now this works and we get a user table so if we refresh we'll see the user table one last note on tables before we move on you'll notice i'm calling it the user table not users the reason for this is that the convention that you'll often see in postgres is that you name tables as singular items and the thought behind that is that you think about tables as this bag that you put things in and because of that when you're talking about it it's really useful to think of it as a user table right it's a bag to contain things so you wouldn't say you have a you would say you have a bag of candy not you have bags of candy right unless you have multiple so that's the thought process and i've seen it broken i've worked at companies before that pluralized tables it doesn't really matter right it's not going to make a big difference on your software itself but there are times when you'll have tools that automatically generate things for you based off of your table names rails and ruby are a great example of that rails will automatically generate a lot of code for you as long as you follow good conventions and the other thing is it's a convention it's what other developers will be expecting from your work so i think it's important to keep them singular whenever possible and if you ever work with or for me you know and i see a a pluralized table in your code i'm going to come at you a little bit you're going to have to be prepared uh for me to shoot down your code review and tell you you need to go change that okay great so we've got our user and profile in place we're going to go ahead and drop the profile table this is really really easy and post secure you can just right click and delete and delete and it's gone uh in order to do that in sql you can just say drop table and then the table name but we have an editor i would recommend using an editor especially as you're getting started with sql and i think post-aco is a great option okay we've got our table in place we're very excited about that now we just need to get data inside we're going to learn some new ones here we're going to say insert into our table name which is user and remember user here happens to also be a keyword so we're going to wrap it in double quotes then in parentheses we specify what columns we want to insert well those columns are email name age and password you might be asking yourself or thinking why why isn't id here we surely want an id for each user well if you remember back to when we were talking about creating a table the id is of data type serial what that means is it's going to automatically generate an integer id for us every new row so we don't have to insert it ourself in fact it would be a mistake to this user that we insert will automatically have the id of one for us because of postgres's serial data type let's go ahead and move on with the query so we've got insert into user the columns we're inserting email name age and password and then we say the values which is a keyword and now we can start to enter those values and how this works is within these parentheses we go in the same order that the columns are listed in these so the order here doesn't matter we can order this however we want we just need to make sure we follow the order after the values keyword so within here so what that means is email needs to come first here when we're entering the value name second age third and password fourth so for email i'm going to go ahead and enter troy at fake.email for name i'm going to enter troy for age 126 and for password i'll enter some nonsense okay this is great we've got our values in place and if we run this it should work but you might have a question what are these single quotes doing here well single quotes are how we tell sql that we're entering some text so this is just looked at by sql as text it's not read as if it was part of the code itself so if we put some sql in here this would get inserted as the actual email or sorry let me use something you're familiar with we haven't gotten to create statement or to select statements yet i'm getting ahead of myself if we put like create table in here this will get inserted as the email right it's not gonna look at this as code it looks at it as text and in fact without it it will look at it as code so if i omit those single quotes it will read this and we'll get a syntax error because it doesn't know what troy at fake.email is we need the single quotes to tell it that this is a string value it's also important to call out if you've done other programming languages before which if you're looking at this course you probably have single quotes and double quotes are not the same here which they are in a lot of programming languages like javascript for example and uh double quotes here are specifically for referencing columns and tables uh and whenever you use them sql will expect you to be referencing one as such and the same with single quotes if we swap these two and it's double quotes here and single quotes here we're gonna get a syntax error it's important to recognize the difference between those two ages and integer integers are not text based data they're not a string so we can just put it you know in line here we don't need to wrap it in anything okay fantastic let's execute that statement we say we've inserted and if we come back and look at our table here and refresh you will notice and i know it's a bit hard to see i'm sorry about that but the table's there and all of our information is in there correctly this is great i'm really really happy that that worked and in fact let's go ahead and insert another user just for practice sake okay i'm gonna insert another user we're gonna make it chris at another.example the person's name is gonna be you're never gonna believe it that's chris he's a notorious 98 year old man who's using her application and his password is wow i love sql 28 and we'll go ahead and insert chris it does work and we can come back and look at our user table and sure enough he's along with troy now all right that's great news we've got two rows in there now you might be thinking about something and i hope you are what do you do if you don't have a nice editor like this how do you get information out and in fact what is this editor maybe doing under the hood to show us this data well friends that's what we're going to talk about next and it leads us to some more keywords the next keyword that we unlock in our journey to sql mastery is going to be select select is a keyword that allows us to fetch information from existing tables it's what postico is using under the hood to show us our entries when we go and we click on the user table here this is how it's grabbing information and remember you're not always going to have access to a nice editor like this and also select gives us additional tools that make it even better sometimes than just viewing the data in an editor and we'll show you that more later on in the video but let's cover the basics for now which of course starts with the select keyword so i'm going to go ahead and write out a basic select statement and then we'll break it down all right fantastic select star from user and let's run it and we can see here in the bottom this is our you know sql output that those rows show up in the user table the user table is there now i want you to think for a moment what does this star probably mean you can probably easily infer what from means select is clearly a keyword that's initiating the statement what do you think this star means pause the video here and contemplate with yourself so when i taught most students would assume that it meant all rows it doesn't it means all columns so this star is referring to what columns you want from user and star means all i can say i just want the name and when we run this we're only going to see troy and chris here it's only giving me the name back i can say i just want the email and it's only going to give me the email or i can do some combination give me the id the email and the name and there you go fantastic that brings up a question we'll go back to select star so we can see everything how do we say that we only want specific entries and it's a good question the way we do this is with the where keyword we can select say select all columns from the user table where and then some condition so my condition might be where the name is troy and if we run this we're only going to see that single troy uh row we can do this with any combination of different conditions so i could also say maybe get me all the users where the age is greater than 20. we'll see both of our users in that case but if we change this to 27 we're only going to see chris there's a lot of different conditions that you can use with a where statement i would encourage you to look up a more exhaustive list than i'm able to go through here but it's extremely powerful and it's our way where we can specify specifically what rows we want to see and then later on when we're talking about update and delete statements where statements become critical because it allows us to specify what rows we want to um uh specifically affect from our queries all right so we've got data in we're able to select it we're able to say you know give me the user with the id of one give me the user with the id of two we have that power now the next thing we need to learn is how do we change data we've got these users in place what happens when we made a mistake troy is not 26 he's 30. and we need to update that user how do we do that all right so we've just unlocked our ability to select information now we need to be able to change it so like i mentioned we want to be able to change this row from age 26 to age 30. how do we do that well in order to do it we're yet again going to need some new keywords those keywords are update and set so the way we construct an update statement is we initiate it off with the update keyword and then we tell it what table we're going to be updating in our case that's the user table then we say what we would like to change i'm going to say set the age equal to 30 okay so we're saying update user set age equal to 30 where and then we use that where statement again remember it from select this is how we uh drill down into it without this if i was to just say set age to 30 it would update every user anytime you're writing an update statement and then later on i'll reiterate for deletes we need to make sure that we're specifying a condition or we're going to make that update to everything it's a really really good way to have some embarrassing conversations in your first engineering job so let's make sure we're using our where condition we'll say where the id is one okay so this is our condition we're saying uh update the user table set the h to b30 where the id is currently one and then the update went through let's use our new skill and let's select star from user where id is one okay and again we have to wrap this in double quotes okay and then we'll go ahead and we'll remove this statement for now we'll come back and revisit it but let's just see if we've got the update going through and we did his age is now 30. that's awesome it worked all right great so that's the basics for update statements i want to make one additional note and it's about this equal operator the equal operator as well as a lot of operators in sql they change based off of the context in which you use them this one here is being used to construct a condition because it's prefaced by a where statement this one here is being used to assign a value and that's because it's prefaced by a set it's important to distinct or to separate those two in your mind and remember that the equal sign operator changes based off of the context in which it's being used okay great that's the basics of update statements we could use this to update any of the columns really but again we want to avoid updating the id but maybe just for a sake of another example maybe we go ahead and we update the email of that same user to be uh troy test dot fake and these are obviously fake emails but let me go ahead and update that and then you know select maybe i just want to see the email from user where id is one and there you go the email has been updated that's fantastic so we've learned how to do almost all of the basic operations there's just one more left and that's removing information what happens when we have a user that we want to delete right this is an applications database we might want to remove users allow them to delete their account entirely how would we do that in sql well the way we do that is with delete statements and they start with a new keyword we say delete and we say what table it's from we'll specify user and then another callout here don't run this query the whole table is gone if we run this query we need a where condition just like we needed with the update query we're going to delete from user where the id equals to maybe our 98 year old friend chris is done using the application we gotta get rid of them so we'll go ahead and delete them again don't run just delete from user in isolation make sure you're specifying the condition so it knows what to delete so we've gone ahead and deleted that user and if we go and we select all from user so we'll get all the users we're going to see sure enough there's only one user left and that's troy all right that's the basic operations with that you can do full crud create read update delete uh operations on any table of your choosing that's the basics the building blocks of sql but now we need to talk about something just a shred more advanced and that's that data does not live alone data is related to one another right you don't just have users in your application and for our application we're going to also have posts let's talk about how to relate information together in sql i've brought you back to everyone's favorite realm it's the powerpoint realm and we're going to talk about table relationships so tables can have relationships with other tables and i'm not talking about the bachelor type i'm talking about data relating to another piece of data so information in our applications does not live alone things have relationships with one another if you think about the site you're on right now youtube each of these videos have comments how does that relationship work how do we relate those things together well the way we relate those together is through foreign keys and those foreign keys allow us to reference specific entities in another table from a table so i'll show you exactly what i mean with one of these examples here let's go ahead and look at our user post example ah that one is actually a bit covered up by me okay let's look at the avadart uh example here so the user table here and this is just real quick i want to touch on what this is this is what we would call an entity relationship diagram so it's showcasing from a high level what how our tables are laid out and related so here we have that user table that we made but you'll notice we've added something new it's the avatar id that avatar id is referencing a row an entity in the avatar table it's a new table and then the avatar itself has a user id that's referencing back to this user's id so it's related in that manner basically what that means is that each new user might have an avatar id so say we have that user one it's troy we all know and love them he's got the avatar id3 what that tells sql is that if you go over to that avatar table and you look for the avatar where the id is equal to three you will find troy's avatar that kind of relationship where a user has one avatar and each avatar belongs to one user that's called a one-to-one relationship there's three different relationship types when we're using relational databases there's a one-to-one like this user and avatar relationship where one user has one avatar and one avatar belongs to one user then there's one to many and this is the most common type and it's the example we'll be using in our code one-to-many is when one entity has multiple of another entity a great example is users and posts another good example is the youtube video we have and comments a youtube video has multiple comments so you might imagine that in youtube's internal system they have a video table and then that video table um you know exists with no foreign key and then there's this other separate comments table that has video ids on each one we'll break those down more in further slides let's jump in and talk about each one of the three relationship types in depth the final ones many to many will get there we'll get there i know it's on the bullet point i don't want to make anyone nervous i just don't have a photo for it let's talk about one to one one to one is the easiest type of relationship to wrap your head around a user here has an avatar id an avatar has a user id so each row in the user table will have an id referencing a very specific entity in the avatar table we talked about this on the last slide but let's reiterate again say you have troy he's got the id of one we all know and love we made him ourself he's now got an avatar id that avatar id references the row in the avatar table in which you will find troy's avatar so if troy has the avatar id3 we would go to the avatar table and look for you know maybe you write a select statement select for the avatar where the avatar id is three and then you'll see troy's avatar is sitting there and just the same in reverse that's the critical part here is that the avatar also has the user id right that user id is referencing back to troy that's what makes it a one-to-one i want to note that although this example is one of the easiest to go through one-to-one relationships are an easy thing to wrap your head around they're actually the least useful and it's something you won't see a ton oftentimes they're used to just avoid column bloat which is when a table gets out of hand because over time you're continuously adding more and more things to an application so oftentimes you'll see it to just avoid bloating the size of your tables by spreading out information across multiple the next type of relationship is a one to many one-to-many relationships are the most common one you're going to see and in fact the next relationship we look like we look at which is a mini domini is just actually two one diminished but let's we're getting ahead of ourselves let's talk about one-to-many so one-to-many is when one entity has many of another entities or they can potentially have many of another entities and let's talk about that example a bit more in the context of youtube in this video this video has multiple comments right but it doesn't need to have multiple comments in fact when you're watching this right now it might not have any comments and that's the case you could be the first one please but anyways the point is the entity which is the video in youtube's case has the potential to have multiple comments even if it doesn't yet and the way that we set that up is through a one-to-many relationship let's jump back to thinking about our specific example which is a user and a post a user has multiple posts and the way we set that up is we keep our user table as is you'll notice it's the same it's unchanged but now we've created this post table and the post table has the user id in a one-to-many relationship it's up to the mini to say who owns it right each entity of that post will tell us who owns that user and the reason is we can't put a post id on a user because that would mean that each user can only have one post which isn't the case the user cannot reference posts the post needs to reference the user when we dive into actually making this post table i'll reiterate that and elaborate just a hair more to make sure you wrap your head around it but in relationships the mini references the one not the other way around and again the reason is that would constrain the user to only being able to have one thing right because if you think about it each of these posts has a user id that user id can only exist once per post so the post belongs to the user if we invert that and the user has a post id that would mean the user could only have one post just like they can only have one name but they don't they don't have a post id here and that uh that omitting of that column allows the user to have multiple posts through this post table let's talk about the final relationship which is a mini to mini this one is a bit more complex and in complicated schemas you'll see it a lot but don't freak out don't freak out that we have three tables now all we want to focus on here is that if you look at these tables in isolation they're actually just two one-diminis which you're comfortable with so all a mini to mini is is it relates two separate tables to each other through one center table and we typically call that table a join table so we've got a one-to-many over here and another one-to-many over here those are related together through a join table the best example i could think of for this was a school setup so you have in a school students or users that are taking classes from instructors an instructor has multiple students and a student will have multiple instructors so how do we relate that stuff together well we relate them together through the class right so we have a user a user has a class here this class as such has the user id remember the user has nothing about the class because it owns the class the class is the one who references the owner now the instructor is also teaching a class so as such they have their instructor id on the class and you'll notice that these are two separate one-to-many relationships but through them we have the ability for a user to have multiple classes and then each of those classes could have different instructors and because of that you could say that a user has many instructors through this class table and that's true in inverse as well okay we're back in postico world and we're ready to create our post table so let's go ahead and start with create table you guys know this now we're going to create the post table think about why i don't have to wrap this in quotes remember back to past and the lecture why don't i have to wrap that in quotes make sure you know make sure you know why i'm going to create the table post it's going to need just like every table an id which is a serial primary key and then we're going to give it a name a name for the post itself maybe this is a varchar 255. you're maxing out at 255 characters for the name then content which we can go ahead and make text and finally that foreign key what relates the post to the user is a user id each post will have the id associated with the user that made said post and we'll go ahead and just make it an integer next what we need to do is we need to tell postgres that this user id is not just a normal integer it's a special integer it's a foreign key and the way we do that is by creating a constraint we can come and say constraint constraint i can type i can text constraint and then we give the constraint a name i'll call it f key fk user which is just a convention it means foreign key for the user then we can say it's a four in key then we tell it what column the foreign key belongs to which is in our case is the user id next we tell it what that column is referencing well it references the user id column and now this here is referencing the table so we'll say user and id so again at the end of our create table we're creating a constraint the constraint is that we have a foreign key the foreign key here is what column we're talking about the user id and then we tell it what that foreign key is for it's referencing the user table id column so we're saying this user id column in the post table is referencing the id column in the user table we'll go ahead and execute that statement and our table is made if we go ahead and refresh post to go here we'll see now that that post table is there and if we look it has this nice structure tab for us just for us to confirm that it worked we'll see user id and i know it might be a bit small but here where it has a constraint where it's referencing it's got this arrow to user.id it's a foreign key we've got it separate or we've got it set up properly okay we made our table with the foreign key let's go ahead and insert into this table so we could just write an insert but remember we have a constraint each post needs to belong to a user so let's just quickly review what users we have in our table and excuse me i have to wrap that in double quotes and there we go we have just the one and they've got the id of one so let's remember that we're inserting for id one so we'll go ahead and construct our post insert now we'll do insert into post and again we don't have to wrap this in double quotes you guys know that the values we'll insert are the name then the content and the user id and we say the values maybe this post is why i love corgis the content is oh my god i love them and the user who made them was user one he's a big corgi fan we can go ahead and insert them and then if we look at the post table we're gonna see sure enough the corgi post is there now let's go ahead and insert some more so we'll bring back i just undid a a bit to get back to the insert post excuse me there so we're inserting the post why i love corgis omg i love them let's just say why i love dogs in general uh so much and it was also made by user one uh what did i do wrong here uh content user id omg why i love it so much i don't see i don't see it do you guys see it you see where i made my mistake got i love them in general oh oh oh oh my goodness something good this is a good call out something good to know about postico is that if you highlight something and hit execute selection it's just going to execute that we want to execute the whole statement okay i'm not i'm not going insane this is this is correct okay we inserted the post now so if we go back and we say select star from post and end it with a semicolon um there we go we've got these two posts in place okay perfect so we've got our two posts in place that's great and we can see that those both belong to user one and that's how we get that one to many right this user one has many posts because we've got two separate posts with the user id one it works now the question is how do we say i want the user along with their posts that seems like an interesting piece of data to retrieve the way that we select data from multiple tables is by leveraging a join now there's multiple types of joins there's an inner join an outer join a left join and a right join but diving into them super thoroughly is probably a good topic for a future postgres video because we're just trying to talk about the basics and for now let's just talk about inner joins so what we can do is we can start from the user table so we'll go from the user table select from user there we go there's our user data and then what we can do is we can tell this table that it needs to also join to the posts table so i'm saying select from users table join it to the posts table so we're going to combine those two tables together and then we need to tell it on what place does it join well it joins on where the post table user id is the same as the user id so we've got these big tables and what we're saying is wherever the post table has a user id that lines up with one of the ids of a user we're going to match those things together so what we'll see here is we'll see two rows because there's two separate posts but for each one they've lined up with the user id one because we're joining wherever the post user id is you know the user id so give me um for a post like this first post all right let's talk about the second one like the second post give me the user information where the user's id in this case one lines up with our post user id column and there we go it lines up so there's our join but you might notice something here we have two name columns which is fine for us viewing it here but what if we're using this in an application and these become variables in some other programming language well you can't have variables with the same name in most languages so it's probably useful to know how we can reference these things differently well remember the star means select everything and we can change it up a little bit we can say select everything from the user table so we'll say user star but then maybe from the post table we'll get the post id the post name but then we'll tell it to not call it post name we'll say call it title as title then we can say get me the post content and get me the post user id now what you'll notice is when we run this it's the same data but it's gone ahead and it's changed and it might be hard to see but now the title of the post or i should say the name of the post is now called title and that doesn't change anything in the table itself it's just changing the results we get back with our select statement so that covers the basics of joints in future videos we'll be talking about more complex joints more complex schema design as well as triggers postgres functions and more complex constraints thank you so much for watching this video if you found it helpful please consider giving it a like and if you have any feedback it would be great to hear about it in the comments down below i hope that this video was useful for you and helps you get started in postgres please consider subscribing so that in the future any additional content i release you're able to get notified for whether that's additional postgres tutorials or just general software engineering advice and tutorials you'll see it all on this channel so subscribing is probably a good idea

Channel: Troy Amelotte

Views: 202,895

Keywords: apollo client, learn sql, pgadmin, postgres crash course, postgresql tutorial, postgresql tutorial for beginners, postsql, psql

Id: zw4s3Ey8ayo

Length: 50min 26sec (3026 seconds)

Published: Fri Jan 14 2022