Installing and Creating Database in PostgreSQL

Key Takeaways

What's going on everybody? Welcome back to another video. Today we're going to be starting our Postgra SQL series. Now Postgra SQL is one of the most popular and one of the best SQL platforms in the entire world. I've compared in the past to my SQL since they're both open source and really popular tools and platforms. But in recent years, especially with the rise of AI, more and more companies are starting to use Postgrace SQL. Now, in this series, we're going to have a beginner, an intermediate, and an advanced. And we're going to do something that I've never done in a previous series before. We're going to get a little bit outside of just querying data. So, in the beginner series, which is where this video is going to be, we're going to be installing. We're going to be learning some of the basics of just using Postgra SQL and querying data. In the intermediate series, we'll get a little bit more advanced. We'll start joining data. We might even start looking at window functions and CTS and things like that. And then in the advanced, we'll go a little bit beyond what we've done before. We're going to start incorporating other functions and functionalities within Postgrace SQL that do not involve quering your data. These may even be things that typically an AE would do, which is an analytics engineer. But as data professionals, we should absolutely know how to do them, how these things work. I hope that's okay, that this series is going to get a little bit more advanced than we typically do because I just think that's where we are. We need to go more advanced. We need to dive into these things and we need to know things beyond just quering data these days. By the end of the series, you're going to be an expert in Postgra SQL. I have no doubt about it. But before we actually start doing all of those things, we have to install and set up our database. With that being said, let's jump over to my screen and get started. All right. Now, most people are just going to come to Google and they're going to say download Postgra SQL. And I'm going to walk you through how to get there cuz it's a little bit confusing, but you can start here and just follow along or I'll leave a link down in the description where you can just go to the final link. Either way is fine, but let's get started. Let's go ahead and click on downloads. You can come over here and select what operating system you are using. I'm using a Windows. So I'm going to select Windows. We're going to come up here to download the installer. This is by the EDB. And we're going to click on this. And now we have our Postgra SQL version. And you can get it for Linux or Mac. And here we have the Windows. So I'm going to leave this link right here in the description down below. So you can just come right to it. But there's going to be a lot of people who don't do that. And I just wanted to walk through that really quickly. So, let's go ahead and select Windows because that's the one I'm using, but be sure to get the right one for you. I'm going to select it. It's going to start downloading, and I'm just going to put this in uh my downloads. Let's go ahead and save this. And I've already have this, but I'm going to get rid of it and replace it again just for you guys. Let's go ahead and click yes. Now that that is downloaded, we're just going to click on it. You can either go to your file explorer and open it from there, or you can just click on it uh depending on what system you're using. It's asking me, you may not have seen that, but it was asking me for permission. I just gave it permission to access the computer. We have our setup window and we just need to walk through this very quickly. And I'm going to make some small notes as we're going through just for you in case you are curious or you, you know, want to make sure you do this right. We're going to say next. You need to specify where this is going to go. By default, at least on a Windows machine, it's going to go into your program files. I am perfectly fine with that. Let's go ahead and select next. Now, this part is really important. I recommend just getting all of them, but the most important ones are the Postgrace SQL Server and the PG Admin 4. You don't have to have the stack builder. You don't have to have the command line tools, but it's just best to have it all in my opinion because you never know what you're going to need it for later on. Let's go ahead and select next. Now, this is going to specify where we're storing our data. And so this is important because when you start storing data within Postgra SQL and you start putting it in there, it has to store it somewhere on your computer to use locally. So I'm just going to keep it in the default file path. Let's go ahead and select next. Now we have to set up a password. This is very important. This messes a lot of people up because they forget their password down the line. Make it really easy. Just make it something that you are going to remember. So I'm going to set mine up. All right, almost done. And now I'm going to select next. And we're going to select the port server. Now, by default, mine's 5432. And so I'm going to go ahead and select next. You can put something else in there, but I'm just going to keep it as the default. Next, we're just selecting the localal to be used for a new database cluster. There's a bunch of different options in here. We're just going to use default. That will make the best decision based off the information that it has from your local machine. And so it usually is best to just use the local one. Let's go ahead and select next. These are all the things that they are going to be changing that they're going to be doing on your computer. You can go ahead and take a look at those if you would like. And go ahead and select next. And now we're ready to actually install this and put it onto our computer. Let's go ahead and select next. And now it's going to install. This takes a couple minutes and then it'll be done. Once mine finishes up, then we'll get going again. All right, Postgra SQL is completely done setting up. We have the option to launch stack builder at exit. We don't need to do that. So, we're not going to. Let's go ahead and click finish. And just like that, we are ready to go. So, what we need to do now is let's come down here and let's type in postgra SQL. Nothing comes up. Uh, and that's because you aren't actually going to be quering directly off of it. You need to go to pgadmin 4. There's our beautiful logo right there for pgadmin 4. That is what we downloaded earlier along with the postgrad SQL server. But to actually interface with it on a local machine, you need to use pgadmin 4. Let's go ahead and click on this. Now it just pulled up. Uh let's go ahead and expand this really quick. It says we need to enter our password. Go ahead and do that. Now I'm going to make sure I got mine in there. I'm going to save that password. I'm going to click okay. I can tell you right off the bat uh when you're setting this up, you're going to need to do a few things. One is you need to come over to this query tool workspace and we need to actually connect to the server because the server is where the data is going to be stored. We're going to actually run our queries and things like that. If it doesn't have it, right, if it's just nothing there, go ahead and click postgraq 18 or you know, if you're watching this later and you need to do 19 or 20 or whatever version, go ahead and do that. We're going to keep it postgraq 18. We do have our port there. We have our database, our user. It should auto select our password. We'll see if it does, but I'm just going to connect and open the query tool. This is a good sign. This is some previous stuff that I've done in the past uh because I was preparing for this series. If we come back over here though to our default workspace, which is where we do want to be. Let's go ahead and open up our server. We're going to come down here to our post grace and we can come down here to our schemas. If we open up our schemas, this is where most of the time we're going to be living. All right, this is our public schema. This is just by default what it's going to look like. There's a ton of stuff in Postgra SQL. We're not going to get into all of it today. As we work through the series, I'll show you new stuff. But if we come right down here, we have our tables. Now, if we open this up, notice we have no tables, right? But we're going to create those in just a second. Let's go ahead and open up our query tool. You can do altshift Q, at least on a uh Windows machine, but if you have a different one, see what that is. And this is where we're going to live. Let's go back to our query. This is typically what it's going to look like. Okay, so we have our query right here. We have our scratch pad over here. And I usually get rid of this if I'm being honest. Um, but then we have our data output messages and notifications down here. This right here and this over here is usually what you're going to be doing when you're working within Postgra SQL. Within here, we can write things like our select statements, our from statements, our where, our group by, window functions, CTE, all sorts of stuff. There's a ton of things we're going to learn in this series. But let's come over here and I'm going to down in the description in the GitHub, there's going to be a file to create the database. I'm going to go ahead and just open this up. And I'm going to open up a file. And this right here is what we need to run in order to create our different tables within this public schema. Now, this is your first time seeing something like this. Might be a little bit confusing, but as I go through and explain it to you, it should make a lot of sense. Now, we have no tables here, but I know people I've done a lot of tutorials like this before. So, I added this at the top because people are going to make some small mistake. They're going to accidentally create these tables but not others. And what we're doing here is just dropping the table if it already exists. So, if there's a table in there and you make a mistake, you don't have to write this or know how to, you know, go in and delete it. You can just drop the table if it's already there. So, you can start from scratch. I did that for you. Now, what we're doing is we're creating a table. These are specific commands within Postgra SQL that do specific things. So all of these kind of red are going to be you know specific commands within Postgra SQL. We're creating this table named ships. We have a ship ID and then right after it you'll notice we have a data type. Now right here we just have serial primary key. So that's just going to be 1 2 3 4 without ever creating a duplicate. That's what a primary key is. We also have ship name and that's just going to be text. Now down below you will see the data that we are going to be inserting into this table. And so you can see this is Millennium Falcon. That's a freighter. And we're going to insert that into this ships table that we're creating. So we're going to create it. We're going to insert it into there. And then we'll insert the values into that table. And everything works well. Let's go down. We also have create table for planets. And again, it's very similar. We're going to do serial text text with these three column names. And this is our data at the row level. And then we have our create table for our character info. Now we have something important here just to note is we have our planet ID. We have our ship ID. These are what are called foreign keys or references to other tables. So this planet ID when we insert this planet ID 2 7 1 this references these planets right here. And so we need to be able to and that's because of the serial primary key just so you know the planet ID just adding that in there for extra info but we have to specify that those are references and just one last thing to note is that this is an integer so we have a different type of data type we also have a date a numeric a character these things for you right now are not mega important but as you're querying data as you're creating databases as you're importing data with thousands and millions of rows data types are super super important. And so this is just a very brief highlight on this. Now, let's go ahead and run this. And you should be able to do this. Well, we're just going to execute the entire script. That means all the code that's in there. Let's go ahead and run this. Right here, it's skipping these drop tables because they don't already exist. It's just giving you this message. So, if you get this message, that's okay. But it returns successfully. Let's come down here to tables and let's rightclick and click refresh. Now we have our character info, our planets and our ships table and they should have columns within it. And you can even come in here and you can look at this. So we have integer text text integer text or actually integer integer but these are our column names and it also shares the data types with it. Now what we're going to do very just briefly because we just got into this is all we're going to do is open up this character info table and take a look at it. And then in the next lesson, we're going to start diving into querying, looking at the basics of querying our data here. Now, let's come up here. Let's click on query tools. It's just going to open up a brand new window that we haven't done before. All you're going to write, this is the only thing we're going to write. We're going to select everything. And where are we selecting everything from? We're going to do from character_info. That's it. It's all we're doing. Let's go ahead and run this. And now we can see we have our data. It's actually in our table. So this is something that we're going to highlight and kind of go into a little bit more in the next lesson. But now we have our tables and they all should be good to go. You know what? Just while we're here, let me go back and or not back, but let's also look at planets. Let's make sure this one got in. So we're going to look at our planets table. The data is in there. And let's also look at our ships data or ships table. And those are in there. So now we're good to go. Whoops. So now we're good to go. We have all of our data exactly where we want it to be. And in this series, in the beginner series, we're going to cover a lot of really good examples for just learning the basics of quering data. It is 100% a necessary skill to know as we get into the intermediate and advanced concepts, especially with learning things that are kind of outside of what a traditional data analyst would typically work on. I will be walking you through every single step of the way. If you've never done this stuff before, don't worry. I will walk you through it to make sure that you know it really well by the end. I absolutely love SQL. I'm so excited for this series. So, I hope that you are going to enjoy this just as much as I am. If you have not already, be sure to like and subscribe and I will see you in the next video.