Hey guys, in previous lessons we saw how we can do creating and reading and inserting.
In this lesson, I want to talk a little bit about the update and delete methods that we can use when
it comes to our database.
So we're going to be covering the alter, drop, update and delete commands in Postgres.
Alter is really used a lot when you want to change the schema of your table.
So you would use something like alter table and then insert the name of the table that you want to alter.
And then you can have a command that does something.
So what's an example of this?
One example is a rename.
We have a table called student and we wanted it to rename it to user.
So we alter the table student.
And then we apply this command.
Another option which is maybe slightly more common is you might want to change something about a particular
column.
Here we have two alter statements.
We want to alter the table.
And within that table we want to alter a column.
And we're altering the schema of the column so we can name the column.
And then we can say, well in this case we actually want to change the data type to something else.
So maybe it used to be text but now we want to change it to Varchar 20.
This is a two layer alter.
Another thing that you can do with alter is you can actually add a new column here.
This command says, let's alter the schema of contact detail and you'll remember that at the moment
contact detail contains telephone number and address these two columns.
But what if we wanted a third column called email?
Well we can alter the table and then we can use the add command to add a new column.
So it's going to be called email.
And it's going to be of data type text.
And that is going to create the new column inside that contact detail table.
Now there's a lot of theory on altering tables.
But let's actually complete an exercise to practice how this might work.
Here's an example.
If we had created a table called example and it has three columns A, B and C, all of which are storing
integers.
Now at the very end, if you remember, we can assign a constraint.
So we can say let's make the A and C entries always unique.
What this constraint does is it says these two a and C must be unique when combined together.
So let's say that in the first case we wanted A to be one, B to be one and C to be one.
What we're saying is we can have A again as one, but A and C cannot be the same.
So we've already got A1A1C, so we cannot have another 1A1C.
So in this case c has to be something else.
For example two.
And this is a really useful thing in many situations.
One example is error catching.
When we created our family travel tracker we have a table called Visited countries.
And this allows us to insert the user ID alongside with the country they have been to.
Previously.
We had assigned that each of these country codes must be unique, but in this case it can't be unique
because two family members might have been to the same country.
Maybe they weren't on the same trip.
But what does have to be unique is that the user ID and the country they went to has to be unique together,
so it can't be FR1FR1, because then we're storing duplicate data using what you know about how we would
create this table and make these two columns unique.
Think about how you can use an alter statement to apply this unique aspect to the country code and user
ID fields in your visited countries table, so you can select your visited countries table.
Click on the query tool and write your query here to apply that constraint.
Pause the video.
Give this a go.
In the previous code, we saw how we would add that unique constraint if we were at the stage of creation.
But right now we need to make an alteration.
So we're going to use the alter table command.
Add in the name of the table we want to change.
And then in this case we want to add a new constraint.
The final code that we need in order to achieve this looks like this.
Alter table.
Name the table.
Add a unique constraint, and inside the parentheses we have our two columns that need to be unique
together.
Just as here we have a and C need to be unique.
Here we're saying the user ID and the country code have to be a unique combination together.
Now going back to Postgres.
If I go ahead and run this command.
If we view our table, nothing much has changed.
But let's remember that the user with id one has already been to France.
If we try to make another insert into the visited countries table, and we're going to insert the user
ID and the country code.
So these are the two columns I'm going to insert into.
And the values are going to be ones that I know already exist in my table, which is user with ID of
one having been to the country of France or FR.
So now if I run this, you can see we get an error and it says that this combination fr and one already
exists and it violates our unique constraint where they have to be unique.
This prevents us from on our front end inserting data that could potentially be duplicated.
And when this error occurs, we can also send a message back to our website and inform the user that
they have inserted a duplicate value.
So alter tables can be really useful for realizing that maybe we need a particular constraint, or maybe
we need to change a particular column, or add a column, or delete a column, and we can change and
update our schema on a flexible as and when needed basis alongside alter table.
The next and slightly more dramatic version is a drop table.
A drop table does exactly that.
It drops the table with a particular name from our database.
You do have to be quite careful about this because as many bad things could potentially happen if you're
not careful.
In the previous case, when I had the SQL code in the queries or SQL in our starting project, and I
told you that you can run all of these commands in order to create the tables as needed, so the user
table and the visited countries table.
But of course, I knew that you already had a visited countries table in your database.
So that's why at the beginning of those commands I added a drop table.
It says drop table if exists, the names of the tables.
And in this case, if you had a visited countries table then it's going to delete it.
If you had a users table it's going to delete it.
But essentially this slightly constrains that drop command.
When we're performing a drop table, it's exactly the same as in pgadmin right clicking and then selecting
delete.
That's pretty simple and pretty straightforward.
Now we've seen some table level commands that allow us to update and delete.
What if we wanted to update a particular piece of data?
Well, in that case we actually use a different command instead of an alter we use an update command.
We specify the table name that we want to update, and then we use a set keyword to say we want to set
a particular column to have some new values, and we can narrow down the particular record in that column.
Remember we have lots of columns of data, but if we wanted to change a particular one of these records,
then we can use a condition to narrow it down.
We can say for example, the ID is equal to a well.
And then in that case, the record that we are updating is simply just narrow down to this particular
line.
And depending on your condition, you can update different parts of your table.
As a challenge, I want you to go into your users table and take a look at the existing data.
You should at this point, if you've been following along with the Family Travel Tracker, you should
have some users in this table.
The goal is we're going to change the name of the first user, and no matter what it is that you have,
we're going to change it to Angelina.
So that's the name that my friends used to call me when I was a kid.
So we're going to perform this using a SQL query instead of using the Pgadmin sort of graphic UI tools.
Using this template, see if you can come up with a way in order to change the user which has an ID
of one, and change the name to Angelina.
Pause the video and give this challenge a go.
All right.
Following along with this template we're going to update the users table.
We're going to set the name column.
And we're going to give it the value that we want to set it to.
The final stage is what record we want to change.
Because obviously we don't want to set the entire name column to all have the data of Angelina.
So we have this condition where ID is equal to one.
Let's run our query in Pgadmin.
Update the users table and set the name column to equal a new piece of data.
Angelina.
And we're going to add the condition where the ID is equal to one.
So now let's run this and you can see the query has returned successfully.
And if we go ahead and view our table, you can see that the first user, the user with ID of one now
has a new piece of data in the name column.
And that's done by our update method.
Now while we're on the topic of changing and updating columns and data, one really useful command that
I want to show you is called Order by.
And this allows us to retrieve data from our database ordered by some sort of condition.
For example, with our users table, what if we wanted to get back the data with some sort of condition,
like an alphabetical order, so that Angelina comes back and then Jack comes back next?
But we can also switch this around so that we actually change it to have a descending order.
We can have it ascending, and we can also have it descending.
And the way that we write our order by is, is by using.
In this case, we're selecting everything from our table and we're specifying which table.
And then we use the command order by and then apply our condition.
The condition in this case for example is the ID column needs to be ascending order.
So if it was number then it would be 1258 whatever it is sorted in ascending order.
Now there is another version which is descending which is DSC.
And that would mean that the data comes back sorted by the ID field.
But now it's actually going to come back in this order.
It's going to come back in a descending order.
As a challenge.
I want you to figure out if you can sort the users table by the name column and sort it in a descending
order.
So right now you can see our first piece of data is Angelina.
Second piece of data is Jack.
Now in your table you might have different data, but hopefully you've got some data.
If not, go ahead and insert it into there, insert some names and just pick a random color that you
want.
But what we want is to write some query so that we can have that data return in descending order, ordered
by the name column.
So pause and have a think about how you might do that.
All right.
So we are selecting everything from the users table.
And we're using the order by keyword in order to sort our data.
That comes back by the name column.
And we want it ordered in a descending order.
If we go ahead and apply this code here and go ahead and hit run, then you can see now the data comes
back sorted by this column, and it's in descending alphabetical order.
Ascending and descending when it comes to text is alphabetical and when it comes to numbers is obviously
from smaller to larger or larger to smaller.
So now let's cover the final command when we want to delete data from our table.
Notice when we wanted to delete a entire table, the command was drop.
Drop the table from the database.
But when we want to delete data then the command is delete.
The template looks something like this.
Delete from and then the name of the table that you want to delete.
And of course you probably want to apply some sort of condition.
An example of this is let's say in our visited countries, one of our users has actually made a mistake
and they've actually never been to a particular country they said they've been to.
So in my case, I've got this entry where user with user ID two has been to a u, a u for Australia,
but they actually never went there.
So Jack has never been to Australia as far as I know, from the law of the 24 TV series.
So let's try and amend that using the delete command.
Look inside your own visited countries table in Pgadmin.
Select at random one of these rows that you want to delete, and I want you to look at the user ID and
the country code.
And I want you to select that row based on its ID and then delete it using the delete command.
Pause the video.
Give that a go.
We're going to be deleting from the visited countries and in my case, the ID or the condition that
I want to select is where the ID is equal to six.
Here this is the entry with an ID of six.
That's the one I want to delete.
I can go ahead and say delete from.
And then the name of my table which is visited countries and the condition where ID is equal to six.
Let's run this and then view all the rows of our table.
And you can see that entry where Jack has supposedly been to Australia has now been removed.
Another option to perform this query is to use the and combination.
So we could say delete from and then it's visited countries.
And we set the condition in this case to say where the user ID is equal to one.
So the first user and the country code is equal to fr.
So this is the situation where user one has been to France.
So now if we run this and we view our data, you can see that condition allows us to specify two parts.
And it means that that entry, that first entry ID one has been removed because it was user ID one country
code fr.
You can use lots of different ways of constraining how you want to delete or update or alter your tables.
And now you have pretty much all of the power of Postgres in your hands, which means you're ready to
start attempting a big project using Postgres.
That's what we're going to do in the next lesson.
I'll see you there.