What is PostgreSQL?

PostgreSQL is an open-source database system that supports relational databases. It is used as a data warehouse in storing data for mobile, web applications and others.

PostgreSQL is an advanced version of MySQL that requires you to have at least bits of skill in MySQL and RDBMS. The latest version of PostgreSQL is 14 which was released on 30th September 2021.

Some of new features in PostgreSQL 14 include;

  • JSON convenient access and multiranges
  • Reducing index bloat on tables with frequently updated indexes.
  • Enhancements for Distributed Workloads
  • Administration and Observability
  • Security Enhancements

In this guide, I will show show you how to install the PostgreSQL 14 on Windows 10 / Windows 11 Operating System and how you create a database using the pgAdmin 4 Interface. We will also demonstrate how to create a table while also inserting, selecting, updating and deleting data.

Let’s get right into it.

Install PostgreSQL 14 on Windows 10 / Windows 11

You can download PostgreSQL installer for windows from PostgreSQL downloads page, find the one that matches your system and click on the download button.

For our case we are working with PostgreSQL 14 on Windows and my machine is x64 OS. If you’re on a 32-bit system, choose “Windows x86-32

After Download, find the setup from where it is located in your downloads then click to Install. The installation wizard opens as below;

Click on next to start the installation. The next step would be to choose the destination folder for the setup.

For the destination folder, I went with the default.

Then select the Components to be installed, by default all components are selected. The next option is to select the directory folder for the data, I still went with the default.

The next option would be to create a password for the super user postgres.

The next step would be to put the TCP port, which I went with the default 5432, Choose the default locale.

Then it shows you the installation summary in the next step. If you have something to change, You can always go back by pressing the Back button.

If satisfied with the settings, click on Next to start the installation.

After installation, the installation concludes and gives you an option of opening the stack builder. The stack builder is used to download and install additional tools. You can choose to continue or not.

For now I chose not to continue, Thus deselect the checkbox and click Finish.

You can find the PostgreSQL folder on the start menu with its components installed.

Accessing PostgreSQL 14 Server on Windows 10 / Windows 11

There are two ways to access PostgreSQL Server

  • 1. Use of SQL Shell (psql), this is the terminal way of accessing the server
  • 2. Use of pgAdmin 4 provides the graphical interface for the server
  • In our case we will use pgAdmin 4

Open pgAdmin 4 by clicking on it form the start menu. You can also move it to your desktop as a shortcut.

It opens as follows and asks for a password. The password is the one you put while installing PostgreSQL. 

Input the password and it will open the PostgreSQL server and to access the server you will have to put the password once more.

Creating Database

Right-click on the PostgreSQL server then click on Create > Database 

It opens as below, where you input the database name then click save.

Creating Database Table

Next is to create a table, dropdown the database tree name in our case Company_ABZ to see the schemas then public then select the table option.

Right-click on the table to create a new table as shown below.

Then the table window opens as below.

Type in the table name, in my case I will use Employee to have the names of the employees in the company

Then click on the columns tab then click on the “+” button to add columns then click Save.

Columns differ to the type of data they represent, Kindly note that so as not to get an error while inserting data.

For example, For data like ID it should be Integer, For name should be a character and so on.

Also note down the Primary key for the Staff ID and the values should NOT be NULL.

After clicking save, you will find your columns under the table name as below

Inserting Data

Right-click on the table, my case its the Employee table then go to scripts then Insert Script.

This will open a script for you to use in creating new data.

The script opens then you have to edit it to put the data as below,

The query returns a successful INSERT operation of 5 rows with the names and IDs of the employees.

To view the data created, go to employee then right-click and select option edit/view data and you can see the data created

SELECT statement

This statement is used to view all data or specific data while setting a condition for filtering the data.

Go to Employee, right-click then go to scripts then go to SELECT script.

Edit the Statement to your liking and it will show you data based on the query. I chose to view all data as below, And here you can see the rows that we added with the INSERT statement.

UPDATE Statement

This statement is used to update data, maybe change a name of an employee, Its more of editing the data to match the current use

Go to the table name, right-click then select Scripts then select UPDATE script

The Script opens and you will be able to edit it out while putting the condition. I wanted to change the name of the employee with the StaffID ‘2’ to ‘Andrew’. Remember form our list above, StaffID ‘2’ the name is Mary.

And here you can view data after the update. See how StaffID 2 whose name was  Mary changed to Andrew.

DELETE statement

The statement is used to delete data in a table, Can be one row or more by setting out the condition.

To delete a row, go to the table name, right-click then go to scripts then delete script

Edit out the script again to delete the data entity that is of no longer use.

And here you can see that employee with StaffID 3 is no longer there.

DELETE Whole Table

This action is used to delete an entire table with all its data.

Go to the table name, Right -click on it then select the option Delete/Drop

It will ask you for a confirmation to proceed with the deletion or cancel the process.

Conclusion

These are just the basic use cases of PostgreSQL leaving out far too many things to do with it. PostgreSQL is fun to use and it being open source is an awesome product to use while learning on how to work with databases.

From a beginner, to intermediate, to an advanced level. It is an awesome software to use, It is direct and does not have complications. Also, with its added new features it makes it fast to process data and thus less backloads.

You should try it.

LEAVE A REPLY

Please enter your comment!
Please enter your name here