If you’re looking to learn the basics of PostgreSQL,
or SQL in general, you’ve come to the right place!
This is the first video in a series of videos that will teach you how to get
started with SQL using the PostgreSQL database.
In this video, you’ll learn how to open the software to access the PostgreSQL database,
create your first table, add some data to the table, and see the data you added.
The first step is to install the PostgreSQL database. If you’ve already got this installed,
that’s great. If not, I’ve got separate videos on how to set it up,
as there are a few different ways and you can install it on Windows, Mac, or Linux.
So, rather than put the installation in this video, I’ve linked the installation videos below,
so you can go and follow those steps and resume this video.
As part of the installation of the PostgreSQL database, a tool called pgAdmin is installed.
This tool is called an SQL IDE or SQL editor and allows you to
access the database and write the code that works with the database.
We’ll be working with this pgAdmin tool for this video.
Also, the PostgreSQL database is often called Postgres for short,
so I’ll use the terms interchangeably in this video.
Let’s get started!
We can begin by opening the pgAdmin application. On Windows, this should be in the Start menu. On
macOS, you can find it in the Applications folder, or search for it using Spotlight.
The pgAdmin application works in the same way on all operating systems.
Once you open pgAdmin, you’ll be prompted to enter a Master Password.
This is something that you specified when you installed the PostgreSQL database,
so you’ll need to enter it here to work with pgAdmin.
Enter the password and click OK.
If you’re not sure what the master password is or enter it incorrectly, the only way I
believe to get around this is to uninstall and reinstall the Postgres database again.
Check the description and comments for more information if you’re in this situation.
Once you’ve entered the master password, pgAdmin will open.
The main screen of pgAdmin looks like this.
Along the top is the toolbar with menus and buttons.
On the left is a panel with a label of Servers at the top.
The majority of the screen is the working area, which we will see more of soon.
The first thing to do once we have opened pgAdmin is to connect to our database.
This will let us work with the database and start writing SQL code.
In pgAdmin, a database connection is called a server, and there should
have been one created when you installed Postgres.
Click on the triangle next to the Servers item on the left
and it should expand with one entry underneath.
In my pgAdmin, I have several servers created, as I’ve been using pgAdmin
for a while for different things. But you should only see one entry here,
called Postgres 14 or whatever version of Postgres that you installed.
The icon will have a red X next to it, as you can see here. Double-click on it, and within a couple
of seconds it should change to the Postgres logo and expand to the next level of items.
Congratulations! You have successfully connected to a Postgres database server!
If you get any errors with this or don’t see anything in the Servers section,
look at the comments below this video for some tips or solutions.
After you have connected to the Postgres server,
you’ll see an entry in the left panel underneath the server called Databases.
Click on the arrow to expand this entry.
In my example, there are three databases: gravity books, postgres, and skylight.
Your server should only have one, called postgres. This is the default database,
and will be enough for this lesson.
The first thing we want to do once we have connected
to our database server is to create a table.
A table is an object on a database that contains data. It’s the fundamental piece of
the database. Creating a table will mean that we have somewhere to store data and view that data.
To do this, we can write some SQL code. We’ll need a place to write that SQL code, though.
In pgAdmin, click on the first icon on the left of the toolbar, called Query Tool.
Once you do, a new tab appears in the main section of the screen.
The main part of the screen changes to a white background with a blinking cursor.
The heading of the tab shows the name “postgres/postgres@PostgreSQL 14”.
This means we are on a Postgres server, and the name of the database is Postgres. This is OK.
The main part of the window here is where we want to focus. This is where we write our SQL code.
Let’s write our first SQL statement.
We’ll write some SQL to create a table. To do this, we write the command Create Table.
SQL is not a case-sensitive language. You can write this in upper case,
lower case, or any other combination. If you’ve seen examples of SQL online,
you might see some people prefer to write SQL in lower case, and others in upper case.
Personally, I prefer to write this Create Table statement, which is also called an SQL keyword,
in upper case. But if you prefer to write it in lower case, go ahead and do that.
After you write Create Table, we need to specify the name of the new table to create.
Let’s say we are creating a table for our new eCommerce website, and the first
table we want to create is for products. So, we’ll call our table name “product”.
Add a space and then type the word “product”. You can call it products, the plural, instead
if you prefer. It’s a commonly debated topic in the world of SQL on whether you should name
your tables singular or plural. I usually prefer singular, but you can call it whatever you prefer.
After the word product, we add an open round bracket.
pgAdmin will automatically add a closing bracket, which is good.
With the cursor in between the brackets, press Enter a few times to add some new blank rows.
Move your cursor back to the first empty row here.
Within our table, we need somewhere to store the data we want to put inside it. A table needs
something called a column, which is a named piece of information within a table. Tables
can and often have many columns, but it needs at least one column. It’s like a spreadsheet.
A spreadsheet without columns has no data, but if there are some columns then you can add some data.
For our example, we’ll add two columns.
So, in the blank row here, we’ll add a new column to this table. First, we specify the column name.
Let’s say we want to be able to record the name of the product. So, let’s call our new column
product_name. We can’t use spaces in the name of a column, so we’ll use an underscore instead.
You’ll notice here that I’ve added this product_name column,
and the table name of product, in lower case.
This is just personal preference, and you can use upper case or lower case if you prefer.
You’ll also notice that they are both in black font. This is because they are not SQL keywords.
An SQL keyword is a built-in word in SQL that has a certain meaning, like Create Table, which
is shown in a different colour. The word product is not a keyword, so it’s just shown in black.
After the product_name, we need to specify the data type of this column and its size.
Why do we need to do this? Well, databases and the SQL language are very efficient, and one of the
reasons is that the data stored in the database conforms to one of the preset types of data.
Our product name is a text value. The data type that we can use in Postgres is called
VARCHAR, which is short for Varying Character. So, enter the word VARCHAR.
Then, enter an opening bracket. We need to specify the size of this column, or the
number of characters. How many characters do we think we need for product name? For this example,
let’s go with 200. The general advice for choosing a size of a varchar column is to make
sure it’s big enough for the kind of values you expect, but you don’t need to make it very big.
After you enter 200 inside the brackets, move the cursor outside of this bracket and add a comma.
This means we have finished specifying information for that column, and we want to add another.
Press Enter to go to a new line, where we can specify our second column.
Our second column will be called price,
and will store the price of our product. So, enter the name of price.
We’ll need to specify the data type for this as well. In this example,
the price is a number, and more specifically a whole number, just to keep things simple.
The data type for this in Postgres is called Integer.
So, use the data type of integer. We don’t need to specify a size for the integer,
because it comes with a default size.
After you specify integer, we’ve finished with the price column.
We don’t need to add a comma because we’re not specifying any more columns.
Finally, go to the end of the brackets for the Create Table and add a semicolon,
which will indicate the end of the statement.
After you have written the Create Table statement, it’s time to run it.
When we run it, the code will be executed and the changes will be made to the database.
In this example, the table will be created.
To run your SQL statement, click on the Execute button here,
which is the triangle that looks like a play button.
When you click this button, the bottom of the screen will show a Messages tab,
with the message of CREATE TABLE, and Query returned successfully.
This means your SQL statement has worked and your table is created.
If you get an error message, it could be for one of a few reasons. You could be missing a comma
or brackets in your query, or not added a space between a name and a data type, or some other
reason in your code. Check that your SQL statement looks like the one in this video and run it again.
If you get an error that says, “relation product already exists”,
this means you have attempted to create a table but a table with that name already exists.
We can solve this by deleting the table and then running the SQL again.
You can delete a table using the panel on the left of the screen.
Expand the postgres database, then Schemas, then public, then tables. You should see a
product table there. If not, right-click on the tables folder and select Refresh.
Right-click on the product table that you see there, and select Delete/Drop.
Click OK on the message that appears.
You can then run the Create Table statement again and it should be successful.
Now that we have a table created, it’s time to add some data to it.
At the end of your Create Table statement, after the semicolon,
press Enter to add a couple of empty lines. Here we will write our next statement.
The SQL keyword to use to add data to a table is called Insert.
So, enter the word Insert, and then INTO.
Then, we specify the name of the table we want to insert data into.
In our example, we want to add data to the product table, so enter the word product.
After product, add an open bracket. Inside the brackets is where we specify the columns that
we want to add data into. We want to add data to both the product name and price columns,
so enter product_name and price, separated by a comma, then a closing bracket.
After the closing bracket, we can go to a new line, and enter the word Values.
We don’t need to go to a new line,
but it helps to make our code readable if we split up our statements like this.
After the word Values, we open some brackets.
Inside the brackets is where we specify the actual values we want to put into the table.
So, what product do we want to add? We need to specify a value for the product_name field first.
Let’s say we want to add a product called Jeans, so we enter a value of Jeans.
Next we want to specify the price, so let’s add a value of 50.
After the brackets, add a semicolon to end the statement.
Our statement looks like this.
Let’s click the Execute button again to run our statement.
We see at the bottom of the screen there is an error message. It says “relation
product already exists”. This means our product table already exists. But,
of course it exists, we expect it to exist if we are adding data to it.
Why do we get this error? It’s because when we click on the Execute button, all of the statements
on the screen are run. This means we clicked Execute, and the first statement that is run
is the Create Table statement. We try to create a table that already exists, so we get this error.
How can we fix this? One way is to delete the Create Table statement,
but let’s say we want to keep it there.
Another way is to just run our Insert statement by itself, and not the Create Table statement.
To do this, highlight the Insert statement, then click on the Execute button again.
This means only the Insert statement is run.
We can see a message at the bottom of the screen. It says “column jeans does not exist”.
It also shows the point in our statement that it refers to,
which is the Jeans inside the Values part of the statement.
What does this mean? Jeans is not a column, it’s the value we want to insert.
Well, this error happens because the database thinks that Jeans is a column,
but we want to add a value of Jeans to our product_name column.
The database thinks it is a column because we haven’t said it is a specific value for our table.
In SQL, whenever we specify a text value like this, we need to enclose it within single quotes.
This will mean the database treats it like a text value,
rather than thinking it’s a column or something else.
So, add a single quote before and after the Jeans value here.
You’ll notice the value change colour from black to a brown or dark orange
colour. This is good, it means that Jeans is now treated as a text value.
Do we need to do the same thing to the price value of 50?
The short answer is no, we don’t, because SQL knows that numbers are not column names.
So, let’s highlight our Insert statement again and run it.
The message at the bottom of the screen should say Insert 0 1, and query returned successfully.
We have successfully added a record of data to our table!
We’ve added the record. How can we see it?
We can write another type of SQL statement to see data,
which is called a Select statement because it starts with the Select keyword. This is
probably the most common type of statement you’ll write, but we’ll start with a basic one here.
Go to a new line and type the Select keyword.
After Select, we need to specify what columns we want to see.
You can specify all columns in the table by using the * character. There is a way to
specify the names of the individual columns, but we’ll cover that in the next lesson.
So, after the * character, type the word FROM. We then need to specify the table where we want
to see the data. In our example, we want to see data in the product table, so type in product.
Add a semicolon, and we are done with our query.
Highlight the query and click Execute.
At the bottom of the screen you should see a small table of results.
You’ll see the column headings here, product_name and price, including their data types that
you specified when you created the table. Product name is shown as “character varying”,
because that’s the data type specific to Postgres that is used when you use the VARCHAR data type.
There is one record in the table, with the name of Jeans and a price of 50.
Well done! You’ve successfully run a Select statement on your table and seen the data in it!
You may want to insert more than one row into your table.
You can do this by writing another Insert statement,
or making changes to the Insert statement you have on your screen here and running it.
For example, we can change Jeans to Dress, and change 50 to 100.
We can highlight this statement and run it, and it should say it was successful.
We don’t need to keep the old statement here, the one that was for Jeans, for everything to work.
Now, highlight and run the Select statement again. You should see the
results at the bottom of the screen that include both the Jeans and Dress products.
We’ve covered quite a few of the basics of SQL in this lesson. You connected to
the Postgres database, created a table, added some data to the table, and viewed this data.
If you have any questions on anything we’ve
covered in this video, let me know in the comments section.
In the next video in this series, we’ll learn more about selecting
data from the table and making changes to data that is already in the table.
I recommend installing your Postgres database and following along by writing
the SQL code in this video to get started with learning SQL.
If you want to learn more about database design and SQL, visit my website at databasestar.com.
If you liked this video, consider subscribing to my channel.
Thanks for watching.