Hey guys.
When we learned about SQL, we looked at how to create tables using the SQL language.
Well, this time let's take a look at some of the specifics when it comes to creating tables using Postgres.
By now, you should be pretty familiar with the idea of tables.
We have tables where there are the vertical parts which are our individual columns.
So imagine a Roman building with lots of columns.
They are always vertical, holding up the entire table.
And of course the columns represent different fields.
For example there's a field called name.
There's a field called age.
And we can keep adding more and more of these fields and the different characteristics of the data that
we want to capture.
For example, in this case, I am building a table of friends because of course, as programmers we
don't have that many friends, so it's good to track them in a spreadsheet.
Now, if you're somebody who has lots of friends, then you'll have an even greater need to track your
friends in a database.
And if you think this is completely ridiculous, don't worry.
This is just an example for us to learn databases and tables.
Now the other aspect of the table is the row.
So these are the horizontal parts of our table.
And I'm just drawing a really bad rendition of a rowing paddle here.
They look like big spoons but you get what I mean.
These are the horizontal parts of the table and they represent individual records of data.
For example, if we had a friend called Angela who is aged 12 and is not very cool, then this would
be our first entry of the table and all data in this row correspond to this particular person or this
particular entry.
We can have as many records as we want and you might think, you know, tables.
Okay, I use Excel spreadsheets all the same.
But one of the biggest downsides of a spreadsheet, for example, is that you can only really have so
many records before the spreadsheet becomes really kind of paralyzed and slow and difficult to load.
Have you ever tried opening up an Excel spreadsheet with over 5000 records?
It's extremely slow to load all of them up, and this is probably the point where people start moving
on to databases because they're extremely efficient at handling lots of data, and you can have millions
of records and it wouldn't even blink.
We've seen this concept of tables.
We know all about tables from spreadsheets, and we now know that when we have a database, essentially
what we have is just lots of tables tied together with some relationships and also with some rules that
define how they should behave.
You've seen previously that we can create a table in the SQL language simply by writing code like this.
There's the key word create table.
And then afterwards comes the name of the table that we want to create.
For example, in the previous version it would be friends.
That would be the name of our table.
And then we have an open parentheses, a close parentheses and a semicolon to define the end of this
block of code.
And in between the parentheses we've got all of the fields that we want to create.
First is the name of the field.
So for example that would have been the name of our friend.
The second field would be the age of our friend.
And the third field was the is cool factor.
And then after each field comes a space.
And then we get to define the data type.
The data type of SQL is a little bit more limited than the data types that are available in Postgres.
But either way you've seen data types like char and varchar, so characters or something like Boolean
for true or false.
And there's lots and lots of other ones that we're going to talk about just in a moment.
If we were to create this table of friends using SQL and creating it inside Postgres, this is the code
that we might use.
Firstly, we've got that keyword create table, and then we've defined the name of the table as friends.
And then in between the parentheses we've got all of our fields that we want to define.
So the first field is the ID.
And the ID is a primary key.
So what this keyword does is it ensures that any of the entries that go into this field must be unique,
and it uniquely identifies each record.
As you can imagine, you might have another friend called Angela, but if you were to search for all
the records with the name Angela, they'll probably be many that will match that criteria.
But we want to be able to always uniquely identify each record in our table, and that is the job of
the primary key.
This field cannot repeat, but in addition, we've got this handy Postgres keyword serial, which means
that if you add a new record now, you don't actually have to specify the ID of this third record.
It will automatically autoincrement from the previous record and give it a number three.
That's pretty handy.
The next field we have is our name field and we've set it to Varchar 50.
So this stands for variable Character.
And what it means is that this field will limit the data size up to a maximum of 50 characters.
But if you have less than 50 characters, say for example, if you just have the word jack for characters,
then it will decrease the size of that data block so that you are efficiently storing your data.
In contrast to, for example, char.
If I had defined this field as char 50, then it is going to give me enough space to store 50 characters
no matter the size of the actual data.
So this Varchar is a slightly more efficient way of storing data.
Another one you might come across when you're using a lot of Postgres is a data type called text.
Now text is also a flexible storage data type.
So it will decrease the size of the data to whatever it is you need, but it doesn't require you to
define a maximum size.
In olden days, a lot of people recommend that we use Varchar and define the maximum data size, because
this is the most efficient way of storing the smallest size of data.
But in modern testing, and I've seen a lot of people running efficiency tests and speed tests and working
with really, really large databases, millions and millions of entries and people are seeing that the
text data type doesn't actually take up all that much more space and isn't actually all that much slower.
It is a little bit slower.
But compared to the gains that you get by not having a situation where in the future you realize, oh,
actually, I now have a friend whose name is super, super long and I am one of those people.
I have a friend where their surname is like 20 characters, and once I had to go and book a flight for
them when we went on holiday and I was reading out the surname of this Sri Lankan friend of mine who
has a very, very long surname, and I started just cracking up on the phone because I had to read out
all 50 characters of their surname.
The point is, very often you'll see a lot of people using the text data type when they want to store
characters over the Varchar, and this is a more modern practice that I'm seeing.
But when you see Varchar 50, this is the old standard best practice way of doing it.
So don't be alarmed.
Either way is fine.
It really depends on your use case.
But in this case, I know that I'm going to refuse to make any friends who have a name that is more
than 50 characters, and in many cases, you are storing data that you know has a maximum data size.
But if you don't and you want the flexibility, it's best to use text.
Now the next one is the age of our friends, and I'm storing it as an integer, a whole number, which
is pretty straightforward.
And then we've got the final one in our table, which is whether if that friend is cool or not.
This is really a binary option, right?
It can only be true or false.
They're either cool or they're not cool.
So we're storing it as a boolean data type.
And we can have that as false or true.
As I mentioned, there are plenty more Postgres data types that you can dig into and depending on your
use case, for example, if you need to store a really big auto incrementing integer for your primary
key because you have billions and billions or trillions of records, or you need it for some other reason,
then you could use big cereal instead of cereal, or for example, when you need it.
To store the character, varying the var char variable length characters, or if you need to store a
date.
And I recommend taking a look at the documentation here and just scroll through the different data types,
because there's a lot of explanation and you can read up more on things such as the different character
types.
As we talked about text char varchar, and it goes into more detail on all of these general purpose
data types.