Now let's try this out for real.
In the current lesson, you'll find a download to this file called capitals dot, CSV and CSV stands
for Comma Separated Values, and you'll see if you open up this file inside VS code, that it's exactly
that.
Each line contains a separate record, and each of the records are separated by a comma to show that
they are in distinct columns.
Effectively, the first line of our CSV happens to be the header.
So it's kind of like the header row defining what is the name of each field.
And there's only three fields id, country and capital.
And in each record the capital corresponds to the country that is Praia, the capital.
Afghanistan is Kabul.
The capital of Albania is Tirana, etcetera.
We've got all of the countries in the world listed in the CSV, and we're going to create a table for
it in our Postgres database, download the CSV file and have it handy because we're going to refer to
this data to figure out how we're going to create our table.
You'll notice that in this case we have a perfect primary key which is our ID field.
Because all of the IDs are unique.
The next is our country field which is the name of the country.
So this is text.
And in our case we can define it as Varchar 45.
Because I know for a fact that all of the countries in this list have less than 45 characters, and
similarly all of the capitals are text, and they also all have less than 45 characters.
Once we have looked at the data and defined each of the data types we want to use, then we're ready
to write some SQL code inside Pgadmin to create a table.
So I want you to go ahead and open up Pgadmin.
And when you first open it up, it might ask you to set a global password.
This is the password for accessing Pgadmin.
And it's important that you write this down because there's lots of passwords involved in databases
for some reason.
And I always get confused which one's which.
So it's a good idea to keep a track of that.
When you go ahead and open up your servers on the left, you can double click on the PostgreSQL 15 database
and it will again ask you for a password.
Now this password is the one that you defined when you installed Postgres.
Remember previously when I told you that when you installed Postgres, it tells you that your default
username is Postgres and you had to set a password.
Now in my case, I set it to 123456.
But in your case, hopefully you've got something a little bit more secure and more importantly, hopefully
you've written this data down.
So now you can go ahead and enter it when it prompts you.
Don't worry if you forgot or something went wrong, you can always reset it, but it's a good idea to
keep track of everything.
Once you're here, I want you to expand this one that says databases.
And by default we get a database called Postgres.
Now, I don't want to use this one because I'm going to create one of our own.
And to do that we're going to right click on databases.
We're going to say create database.
And we're going to give our database a name.
So my database is going to be called world.
That's it.
The owner is our default user Postgres.
And we can go ahead and simply hit save.
So now we have our two databases Postgres and World which we just created.
If you go ahead and expand schemas and then take a look inside tables, you'll see that there are currently
no tables.
So let's go ahead and create a table.
Having selected this database, I want you to go ahead and click on this query tool.
This is the place in Pgadmin where we can write SQL code.
As you might remember from previously, in order to create a table, we write create table and notice
how it's very helpfully highlighting your keywords.
So if you somehow make a typo like create table, then you should be reminded that there's something
wrong because it's not highlighting it correctly.
Let's create a table and I'm going to call it capitals.
And once we've added the name I'm going to add a set of parentheses.
And at the end of the parentheses it's important to remember to add a semicolon.
Unlike JavaScript which is kind of nonchalant about these kind of things.
It's like, oh, you missed a semicolon.
Sometimes it can, sometimes it doesn't.
In SQL, it's very important that you end each of your lines of code with a semicolon.
Otherwise it's not going to work.
Now we get to define our fields.
So remember our first field was ID.
And we're going to give it the serial data type.
Now in our case it doesn't really matter because we're going to import data that is already serial that
we've already defined.
But it's good practice.
Whenever I'm creating a primary key, which is usually called the ID field, I like to have it named
as serial in Postgres, because then if I add any new entries, it's going to automatically work this
out so I don't have to look through the database.
Look up what's the ID of the last entry?
Figure out this kind of stuff.
We're going to leave it to the computers.
Now the next constraint I'm going to add is of course our primary key.
There's lots of constraints in Postgres, such as the unique constraint or the not null constraint.
And we'll see this in later lessons ins when we cover these different constraints.
But for our ID, the only constraint we need to tell it is that this is the defining field for each
record.
It will be unique for every single record.
That's our promise to Postgres.
The next one we want to call it country, and it is going to be a text data type.
As I said, it's not going to be more than 45 characters, so I'm going to define it as a varchar brackets
45.
And similarly for the capital, I'm also going to define it as a Varchar 45.
And remember that weird aspect of SQL.
The last field cannot have a comma, otherwise it will give you an error.
All of these other fields have a comma, and the last one you leave it blank.
A couple of things.
Little.
Quirks to note, but this is essentially the code that we need to create our table.
Let's go ahead and run this code and hopefully you will have something green.
And also it says query returned successfully.
This means our table is created.
But where is it.
Well let's go ahead and right click on this.
Click refresh and hopefully you'll see your capitals table.
If you right click on this and say view Edit Data or Rows, then Pgadmin is going to bring up your table.
Now our table is a little bit sad at the moment because we have no data in it.
All we did was to create the table.
Notice how our query area changed.
It changed to select asterisks, which means select all from public dot capitals order by ID ascending.
This was SQL code that was written automatically for us by Pgadmin when we simply right clicked on view
edit data or rows.
Now let's see what happens.
If I say first 100 rows, it's going to change the query to say order by ID ascending limit to the first
100 records.
As you can see, using Pgadmin is a really great way to get access to a gooey or a graphical user interface
where we can click and point and do all the usual things you would expect when working with a computer,
and without having to do all of this manually by typing out SQL code, which, as you can imagine,
might have errors you might forget.
ET cetera.
So this is a really great way of working with Postgres, and I'd recommend everyone to use this rather
than trying to access Postgres through the terminal or something crazy.
Now that we've got our table successfully created, I want to show you one last thing before I leave
you to do an exercise.
And that is how we can import data into our table.
You know that you've already downloaded the capitals CSV, and we can import that data format CSV into
our table in Postgres.
And we can do that using Pgadmin in a really simple way, much, much simpler than if we had to do this
manually.
Right click on your table and then go ahead and click on Import Slash Export data.
Make sure that here where it says import export you've got import selected by default I think it goes
to export.
And then we can select our file name.
So if you go ahead and click on this folder and find where you saved that file.
Capitals dot CSV probably in your downloads folder.
Then go ahead and click open and it will add the file path to that file.
Leave the format as CSV, and then go into the options and make sure this checkmark next to header is
clicked blue to the right.
What this is going to ensure is telling our import that the first line in our CSV is not actual data.
It's not a record.
Instead, it's the header for each of the fields.
So now when you go into columns, you'll see that it's auto imported the names that's in that CSV and
it's added it in here, the ID, the country and the capital.
Remember that when we created the table, I created the names of the fields as exactly matching the
header.
This is important if you have changed your field names from, say, a capital C instead of lowercase
C for country, or you've called capital something else, then it's important that you either change
your CSV or change your table, because otherwise something might go wrong and it might not know which
one you're talking about, which columns to import, etcetera.
If we go ahead and click okay, then it's going to perform the import process.
And hopefully you're getting all green pop ups saying process completed.
And if you had any issues such as you need to rename your fields, you can simply right click click
on properties, go to columns and then edit each of the names here and make sure you hit save.
And once you've got all of that matching, then you can try the import process again.
So now let's go ahead and right click on our table and click refresh.
And we can go to View Edit Data or Rows.
And you'll see we now have lots of data in our database.
And it's import.
And all of those countries and their capitals.
Now we can go ahead and use this data in our database, which is what we're going to be doing in the
next lesson.
We're going to be building out a world capitals quiz.
But before we do that, I want you to get a little bit more practice doing this.
And so I have created another set of data for you to create a table and to import it.
The exercise for this lesson is to download the other file in this lesson, which is called flags dot
CSV.
And if you open that file up in VS code, you can see what the data looks like because it's also a comma
separated value type data.
Here we have the header.
It's got id name and flag.
And then we have each of these records.
For the flag it is an emoji.
An emoji is basically a UTF eight data type.
It is essentially stored as text.
And when that text is recognized by a computer, then it will know to pick an image to display in its
place.
Now you can either store this data type as a Varchar, but I recommend using the text data type because
it handles UTF eight a little bit better and you get to try out a different data type.
Pause the video now and try and create a table in your Postgres database.
You can call the table flags and go ahead and add all of these fields in your create SQL code, and
then import this file into that table and hopefully everything going well.
You'll have another table with all of the data of each country and their flags.
So pause the video.
Give that a go now.
All right.
Just as a reminder, the way we're going to do this is we're going to the same database, our world
database, and we're going to create a new table.
So making sure your world database is selected.
We're going to open up our SQL query panel so that we can write our code.
So create table and it's going to be called flags.
And then we can add our fields.
Remember to add a semicolon.
And then we can write ID serial primary key and then name which is the name of the second field.
So we're trying to keep these headers matching with our Postgres database fields.
And this I'm going to store as a varchar 45.
And then finally the flag is going to be saved as a text data type.
Again no comma for the last piece of data.
And we can click run to run this code and create this table.
Let's go ahead and refresh our tables.
And we can see we now have a table called flags.
Now you might have realized that there's another way of creating tables in Pgadmin, which is to simply
right click on Tables Create table.
You can add the name in here.
You can define each of the columns.
And you can do that by simply adding a new row.
But I think in this case it's actually better doing it using the SQL query, not only because we've
learned a bunch of SQL, so we should use it, but also I find it much quicker to type out my schema
than to define it by clicking.
There are other cases where, for example, when I just want to view a table, when I want to view some
data, then you can click around.
But I recommend typing out the SQL code if not for anything else, just for practice.
Now we need to import our data.
So right click on our flags table and then click on Import Export Data making sure it is import.
Making sure that we change the file path to the location where we've saved our flags dot csv.
Click open to save that file path and you can check by scrolling down.
It should say flags dot CSV and not the previous version.
You'll get an error if you try to import the wrong shape of data, of course, and then making sure
header is selected to true to the right, and then columns making sure all three columns are in here.
And then click okay to import our data.
Now let's right click on the flags and then view all rows.
We can see all of our data has been added here.
And we can even see our little flags which is perfect.
Hopefully you manage to do that.
All right.
And in the next lesson we'll be using these databases that we created.
And we're going to be reading the data in our database to create an actual quiz app.
So for all of that and more I'll see you there.