PostgreSQL is a very well-known and widely used Database Management System in the Web Development world. Its Databases are relational, very similar to what we see in MySQL, for example.
It was released in 1996, but its development began in the 1980s. It is an open-source project and has many advanced features, such as foreign keys, triggers, and stored procedures. It is defined by its documentation as “The world's most advanced open source relational database.” Its latest stable version is 12.3.
In this article, we will learn how to install it and create our first example with Postgres.
Installation
Linux
The installation on Linux can be done through apt-get, using the following commands:
sudo apt-get update
sudo apt-get install postgresql
Mac
PostgreSQL can be installed on Mac using Homebrew, with the following command:
brew install postgresql
Windows
We can install Postgres on Windows through the command line, using Chocolatey. We teach how to install this Package Manager in the article Getting to know Chocolatey: the Windows Package Manager.
If you wish to use Chocolatey for installation, you can use the command choco install postgresql. However, if you prefer to install it from the PostgreSQL website, you can access the downloads page of the Postgres site.
On this page, we will choose the download of version 12.3, for the Windows Operating System:
After finishing the download, we will run the installation file.
In the first window, we will press “Next".
In the next window, we will choose the folder where PostgreSQL will be installed.
The next step will be to choose which features we want to install with Postgres. In our case, we will leave all options checked.
In the next window, we can choose the folder where Postgres data will be saved. We can click on “Next”.
The next window will ask us to set the password for the superuser of PostgreSQL, which in this Database is called postgres. We can set any password we want.
The configuration we will define next will be the port that Postgres will run on. By default, the port will be 5432, and we can leave it as is. Therefore, just click on “Next”.
Next, we will choose the language that we will see in Postgres. We will choose Portuguese from Brazil.
The next window will show us a summary of the features that will be installed on our computer.
Next, we will see the option to start the installation. Just click on “Next” and the installation will begin.
Once the installation is complete, the completion window will be displayed. Just click on “Finish”.
Creating the first Database
To create our first Database, we need to start the graphical interface of PostgreSQL. It is called pgAdmin. Unlike MySQL Workbench, which is a program installed on our computer, pgAdmin starts a Web server on our localhost. You can find it in the Start Menu:
The server will open in our browser. Let's expand the option Servers > PostgreSQL 12.
In this menu, we will right-click on the “Databases” option and click on Create > Database...
We will name our Database “hcodedb” and click on “Save”.
After creating our Database, let's create our first table. To do this, we will click on the “hcodedb” Database and then click on the “Query Tool” button, which is next to the word “Browser”.
A window will open where we can execute our queries in the Database.
To create our first table, we will use the following script:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(256) NOT NULL,
email VARCHAR(256) NOT NULL
);
Note that our column “id” is of type SERIAL. This is a data type in PostgreSQL that defines that this field will be of type AUTO_INCREMENT, which we already know in MySQL.
To execute this script, we will select the lines of the query and press the F5 button, just like in SQL Server.
After creating the table, let's add the first data to it. We will use the following script for that:
INSERT INTO users (name, email) VALUES ('Anthony', '[email protected]'), ('Glaucio Daniel', '[email protected]');
It is important to select only this line before pressing F5, so that the table creation script is not executed again, which could generate an error.
Now that the data has been inserted, we can see it in our interface using the command below:
SELECT * FROM users;
With this command, we will see the following result:
Excellent, the data insertion was correct!!!
In this article, we had an introduction to what PostgreSQL is and created our first Database. Notice that, as Postgres uses SQL language, there are many similarities with MySQL. Thus, if we already have some knowledge of MySQL, we will not have difficulties in learning Postgres and using it to design the data structure of our Web systems.
If you liked this article, don't forget to share it with others. You can send topic suggestions by clicking here.
Thank you for reading and see you in the next article :)