(soft music)
- [Instructor] In order to get started
working with Postgres, we need two components.
First, the Postgres server that'll house our data,
and manage connections, security,
and maintenance of the data.
Second, we need a way to communicate with a server
so that we can work with and view the data that it contains.
For this, we'll need a management application
or what sometimes referred to as a front end client.
There are lots of different clients
that we can use to interface with a Postgres server.
But the easiest way to get started,
is to use an installer that gives us both components
in one step.
We'll start the installation at the URL, postgresql.org.
This is the official site for Postgres,
and there's lots of good information on this page.
You can find information about joining some user groups
or the latest news about development.
I recommend bookmarking this page and coming back
when you have some time and simply explore
all of the resources that are available.
For now, though, let's scroll back up to the top of the page
and click on the big download button.
There's a number of different application installers
that are available depending on what operating system
you're working with.
If you're working on a Linux machine,
it's likely that you already have a version of Postgres
available to you, since it comes pre-installed with the OS.
The instructions you'll find that these links
will help you install an alternate version If you need to.
The installation for our macOS and windows
is almost identical.
I'm working on a windows computer.
So I'm going to click on the windows link.
That takes me to a page that describes
what versions of Postgres are available on each platform,
either a 64 bit or 32 bit.
The current version of Postgres
at the time of this recording is version 12.
After viewing the platform support information,
you can click on the link up here that says,
download the installer.
This takes you to a page that's maintained by a company
called Enterprise DB.
Finally here, we can choose which version of Postgres
we want.
I'll choose the downloader for our Postgres 12.2,
by clicking on the download link underneath windows.
If there's a newer version available for you,
feel free to download that instead.
It shouldn't make a difference for this course.
Once the download is completed,
as I can see down here at the bottom of my screen,
we can go ahead and close the web browser
and start the installation process.
You should find the file inside of the downloads folder
for your computer.
Go ahead and double click on it to start it up.
I'll allow it to make changes to my computer.
And while that's starting up,
I'm going to close out of the downloads folder.
So that starts the setup wizard,
let's press the next button on this screen.
The default installation directory
is going to be on my computer
inside of the program, files folder,
a new folder for PostgreSQL, and then the version 12.
I'll just leave the default and press next.
Here we have four different components that we can install.
The PostgreSQL server is the main component
so I want to make sure that that's turned on.
We have to interface clients that are coming along.
pgAdmin4 is a graphical user interface,
and I'll leave that on, as well as the command line tools.
and I'll leave those on as well.
Stack builder is a tool that'll help you install
additional add on packages,
if you're interested in extending Postgres capabilities.
I'll go ahead and press the next button.
Next it sets up a default data directory.
And once again on my computer,
that's in the program files PostgreSQL 12,
and then a new folder called data.
I'll just choose that default and press next.
Now, in order to connect to the server,
we'll need to have a user account.
Here the installer is creating a default user
that's named Postgres,
and we need to create a password for this user.
You can type in whatever pass you'd like,
just make sure that you remember it moving forward.
This user account
is going to be an administrator level account.
So this user will have permission to do anything
on the server.
Once you've typed in the password
and re-typed the same password, press the next button.
The default port that Postgres will communicate on is 5432.
We'll just leave that there and press next.
And on this screen, we get to choose a default location.
Once again, I'll leave this as default locale
and press next.
That'll give me an installation summary.
I'll press the next button
and then next one more time to install the software.
When that finishes up the database server
and client applications have been installed.
Now, if you chose to install
the stack builder application as well,
you can turn off this check box here.
We don't need to run stack builder,
when we finish out of this wizard.
I'll go and press the finish button,
then I'll dismiss everything.
And now we can go into my start menu
and I'm going to scroll down
and find the new folder that was just added
for PostgreSQL 12.
Inside of here, we have a couple of applications.
The ones that I want to focus on our pgAdmin4,
this is a graphical user interface for Postgres,
that'll run inside of your web browser.
I'm going to right click on that and choose pin it to start
for easy access.
The other one I want to look at is called SQL shell,
or PSQL,
I'm going to right click on that and pin that
to the start menu as well.
This is a command line tool that's popular with programmers.
On macOS you'll find the Postgres SQL 12 folder
inside of your applications folder.
And inside of there will be the same pgAdmin
and PSQL applications.
So now we have Postgres server,
as well as a couple of client applications available for us
to work with.
The PSQL shell is a command line interface for
working with Postgres database servers.
It provides a quick way for administrators
to log into the server and run commands,
but it's not the most user-friendly environment
for new users since it requires that you know,
what commands do you want to run and how to type them out?
However, I do think that it's important to see briefly
in order to help reinforce the idea
that the client application is completely separate
from the database server.
And PSQL is a very important tool
in the world of Postgres development.
So let's go ahead and click on it to start it up.
When you first run the tool,
it opens up a command line window
and starts the login process.
In order to log into a Postgres server,
you need to know some connection details.
First, it asks for the server location.
If you're in a typical office environment
where the Postgres server is running
on a centralized computer,
they don't need the IP address of that machine.
In our case, we're running the server and client
on the same physical machine.
So we can use the word local host instead.
You can either type that in or simply press enter
and local host the default
as indicated by the text in square brackets
will be used instead.
I'll just leave this blank and press enter,
to enter in local host.
Next it asks you which database you want to connect to.
Each Postgres server can hold many different databases.
Our server is brand new and there's just one database
called Postgres.
So I'll log into that one.
Again you can press enter to accept the default value
of Postgres.
Next we need the communication port
that the server is listening on.
This was set up during the installation step,
but it's typically left at the default of 5432.
I'll press enter to accept that value.
Then we need to provide the user account credentials.
Again, during the installation,
we created a super user account named Postgres.
So I'll use that.
If you've been assigned your own personal user account
for your server,
you would supply that username here instead.
And finally, the user's password.
Again, we gave the Postgres user account a password
during setup.
So I hope that you remember
what you filled in during that step.
Go ahead and type it in now.
When you type, it's not going to appear on the screen,
so just type it out and press enter when you're done.
If everything was filled in correctly,
you should be connected to the Postgres server
and the command prompt will change.
Now we can start sending commands to the server.
One thing that we can do is get details
about the installation.
We can do that by running select version
and then an open and close parentheses.
Select is a SQL command that returns information,
and we're using a built-in function called version,
to pull out the server version
and installation platform information.
All of these commands are going to finish with a semi-colon
to tell the server that we're done typing,
and to go ahead and execute the statement.
When you press enter, you should get a similar result.
I am working with Postgres version 12.2,
and it was compiled by visual C++ on windows.
Another command that we can send is to ask
for the current date and time.
That uses a function called now.
So once again, I'll say select the function now,
open and close parentheses and a closing semi-colon.
If you were to omit the semi-colon Postgres doesn't know
that you've finished with the command,
and when you press enter,
it simply goes down to line number two.
You can type the semi-colon here on line number two,
and Postgres will then execute your statement,
when you press enter.
That'll reveal the exact time and date
that I'm recording this movie.
So we can run commands on the server
and get back some information.
Let's create a database to work in.
We can do that with the create database command.
We'll follow this with the name of the database
that we want to create.
Let's call it favorite colors.
When you press enter,
it returns create database
that tells me that the database was created successfully.
To see a list of all the databases on the server,
type backwards slash, and then a lowercase letter L.
This is an SQL command,
so you don't need a semi-colon at the end, just press enter.
And you'll see a list of all the different databases
that are on the current Postgres server.
There is the default Postgres database.
We have a couple of template databases,
and then we have the database favorite colors
that we just created.
In order to work with the favorite colors database,
we need to switch into it.
Remember that when we logged into the server,
we logged into the Postgres database.
To switch contexts, use backward slash C,
and then the name of the database, favorite colors.
That tells me that I'm now connecting
to the database sever colors as the user Postgres.
So we've disconnected from the first database
and connected to the second.
You'll notice that the command prompt has changed
to indicate that the database that we're connected to
is now favorite colors.
At this point, we can add a table to this database.
We do that with the create table command.
The table I want to create is called colors.
And then I'll open up my parentheses.
In order to create a table,
we need to define the different columns
that'll make up the table.
The first column in this table will be called color ID,
and it'll store integer values,
or INT, these are just basic numbers.
So that defines the first column in the table.
I'll type in a comma,
and the name of the second column in the table.
I'll call it color name.
This column will store text data or character data.
I'll type in char.
And I want it to be able to store up to 20 characters.
So inside of parentheses,
I'll type in the number 20 and a closing parentheses.
That finishes the definition of the two columns
for my table.
I'll type in another closing parentheses
and a closing semi-colon.
Let's press enter.
And Postgres tells me that the table was created.
With the simple table in place,
we can start to store some data into it.
To do do that, we use insert into.
I'm going to insert into the colors table,
and I'm going to insert some values.
This will allow me to add rows of data to the table.
I'll open up a parentheses and the first row
will be the number one, followed by the text read.
Text in Postgres is going to get entered
inside of single quotation marks.
So there is my first row.
I'll close out the parentheses,
type in a comma,
open a parentheses,
and we can type in the values for the second row of data.
This will be two, and the text blue.
Let's add one more.
The number three, followed by the text green.
That finishes the insert into statement,
so I'll close the semi-colon and press enter.
Postgres tells me that three records were inserted.
And now I can review that information
by typing in select star from colors.
This command asks for everything from that table.
And we can see that the table is made up of two columns
and that it contains three pieces of data.
So what we've just done is write out several commands
in a language called structured query language or SQL.
And it's the primary way that database developers and users
will interact with relational databases.