pgSQL - The right way to get started

SQL can be used to create, update, and delete database records. It can also be used to query data from a database. It can be used with a wide variety of database management systems, including MySQL, Microsoft SQL Server, Oracle, and PostgreSQL.

In this blog from AlmaBetter’s team we are going to learn the right way to get started with PostgreSQL. Read on!

What is PostgreSQL?

PostgreSQL is a powerful, open-source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

It is the default database for many popular applications, including GitHub, Redmine, and Discourse.

PostgreSQL is known for its high performance, scalability, and rich feature set. It supports both SQL (structured query language) and NoSQL (non-structured query language) data, allowing it to power a wide variety of applications.

How to Install to PostgreSQL:

Install PostgreSQL on Windows:

• Download PostgreSQL from https://www.postgresql.org/download/windows/
• Run the installer and follow the instructions.
• Download pgAdmin 4 from https://www.postgresql.org/ftp/pgadmin/pgadmin4/v4.22/windows/
• Run the installer and follow the instructions.
• Open pgAdmin 4.
• On the left panel, expand the Servers group and double-click the PostgreSQL server to open the connection dialog.
• Enter the following details: 1. Host name/address: localhost 2. Port: 5432 3. Maintenance database: postgres 4. Username: postgres 5. Password: postgres
• Click Save.
• Double-click the server to open it.
• Expand the Databases group.
• Double-click the database to open it.
• Expand the Schemas group.
• Double-click the schema to open it.
• Expand the Tables group.
• Double-click the table to open it.
• The data will be displayed in the Data Output tab.

PostgreSQL on Linux:

• Open the terminal and type: 1. $ sudo apt-get update 2. $ sudo apt-get install postgresql postgresql-contrib
• To verify that the installation was successful, type the following command, which will print the PostgreSQL version: $ postgres --version
• The output should look something like this: postgres (PostgreSQL) 11.1
• By default, PostgreSQL creates a user named “postgres” with the role “postgres”. To connect to the PostgreSQL database server as the postgres user, type the following command: $ sudo -u postgres psql
• To install pgAdmin 4, type the following command: $ sudo apt-get install pgadmin4
• To launch pgAdmin 4, type the following command: $ pgadmin4
• pgAdmin 4 should now be up and running in your web browser.

PostgreSQL on macOS:

• Download PostgreSQL from the official site - https://www.postgresql.org/download/
• Select the “macOS” tab and choose the appropriate installer for your system.
• Run the installer and follow the prompts.
• Once the installation is complete, open pgAdmin 4.
• In the “Create New Server” dialog, enter the following information: 1. Name: “My Server” 2. Host: “localhost” 3. Port: “5432” 4. Username: “postgres” 5. Password: “postgres”
• Click “Save”.
• You should now be able to connect to your server and view your databases.

PostgreSQL – Loading a Database:

We are going to see the process of loading a PostgreSQL database into the pgAdmin4. Before moving forward we just need to make sure of two things:

• pgAdmin4 is installed on your system.
• A sample database.

In this blog, we will be using a sample database which is a DVD rental database. You can download the sample dvdrental database from here.

The Sample Database:

So, the DVD rental database that we will be using ahead in the article represents a DVD rental store. The objects in the database includes:

• 15 tables
• 1 trigger
• 8 functions
• 1 domain
• 7 views
• 13 sequences

In this blog, We are using a PostgreSQL sample database that you can use for learning and practicing PostgreSQL.

We will use the DVD rental database to demonstrate the features of PostgreSQL. The DVD rental database represents the business processes of a DVD rental store. It has many objects, including:

• 15 tables
• 1 trigger
• 7 views
• 8 functions
• 1 domain
• 13 sequences

ER Model of the sample Database:

Tables in the Sample Database: There are 15 tables in our sample database which are listed below:

• actor – stores actors data including first name and last name.
• film – stores films data such as title, release year, length, rating, etc
• film_actor – stores the relationships between films and actors.
• category – stores film’s categories data.
• film_category– stores the relationships between films and categories.
• store – contains the store data including manager staff and address.
• inventory – stores inventory data.
• rental – stores rental data.
• payment – stores customer’s payments.
• staff – stores staff data.
• customer – stores customers data.
• address – stores address data for staff and customers
• city – stores the city names.
• country – stores the country names.

So now we know everything about our sample DVD rental database, let us move on to loading the same database to the pgAdmin4. The steps to which are listed below:

Step 1: Open PgAdmin

Step 2: Enter the password for the user ‘postgres’ to connect to the server

Step 3: Create Database with Name as “dvd_rental”

Step 4: Restore .tar file into the database

Step 5: Now we are going to view tables in our database.

As you can see we have loaded the Sample Database into PostgreSQL with pgAdmin4.

Conclusion: PostgreSQL is one of the most popular relational database management systems (RDBMS) in the world. It is an open-source project that is not controlled by any one company. PostgreSQL is known for its reliability, feature richness, and performance. In our upcoming blog, we will talk about “Getting Started with Random Variables for Data Science”.

Read our previous blog on Introduction to Python Pandas for Beginners.

To become a skilled Data Scientist, join our Full Stack Data Science program today.