#5 Introduction to PostgreSQL: Creating a New Database
Sign in to perform actions on this cast!
So in the last video we briefly went over how to delete and culture role. And in this video I think we are ready to create our own database. So again we can do P. S. Q. L. A log in to check what roles we have. So we do have a test one but it does not have a attribute that allows it to create database. So we're gonna write culture rope test one with great. Yeah these. Oh I think I just made a syntax error. So let's check what is wrong. So I think I was supposed to type create leave me instead and let's check our user again. So as you can see we have test one with now the attribute of create database but now we are locked in in my main which is Elena and we don't want to create a database of that. So black slash Q. To quit. Let's re log in. So one way to log in and create database is by typing with your postgres. Test one. So the way you log into your main and to your other role users are different when you want to create database. So make sure that you type es que postgres and dash U. For user and the user name that you want. So let's do that and you can see that we've logged in as postgres. That is fine because whatever we create will be under test one. So now to create database let's call it test database. Remember the semi colon. And as you can see we have created our database but to make sure that our database belongs to test one and our postgres we can type backslash list. This will show us a list of database that are created and based on what users they belong to. So as you can see I have a lot of data base because I've created a lot of projects and you can see what are the owners for the database that they belong to. So in here we can see that test database that when we just created belongs to the owner. Test one and not postgres. So that's when you know that we created the right database for the right user. However if say we want to drop the database we can do it in a similar fashion. So all this impacts is very similar. If you can take a guess it would be drop database test database. So let's take specialists again. And you will see that we no longer have anything for owner test one because we have dropped the only database that we made for it. So again let's create the database Back and see that belongs to Test one. However, once you have created a database and you want to delete a user, it is not so simple. So for example if I want to drop the road text one right now it would give you an error permission denied to drop real. Oh that is because I'm not in my main. So that's quit T S kilo draw test one. So test one cannot be dropped because some objects depend on it. This means that there's a database that depends on test one. So before your daily test one you would have to drop the database. Test database first. So let's log back in to our test one. Let's type drop database text database. Let's check it, It's gone. And then now we can go back to our main user and drop bro. Test one now it should work. So let's check again. You'll see that test one is gone. Oh in SQL database depends on the user. You cannot drop a user if the database already exists that is to prevent you from deleting a whole database by accident.