Prerequisites

You know basic SQL.

You want to learn PostgreSQL for your upcoming project

Introduction to PostgreSQL and Its Significance

PostgreSQL, often referred to as Postgres is a powerful, open-source object-relational database management system (ORDBMS). It's a favourite among developers, and there's a good reason for that.

Let's dive into what makes PostgreSQL special and why it might be the right choice for your career.

PostgreSQL is a database system that uses the SQL language, but with some unique twists. It's known for its robustness, scalability, and flexibility. Unlike some other database systems, PostgreSQL supports both SQL (relational) and JSON (non-relational) querying, making it incredibly versatile.

PostgreSQL is one of the best database systems for many use cases. Here's why:

1. Open Source: Being open-source means that it's free to use and has a strong community of developers constantly improving it. You can even contribute if you feel like it!

2. Performance: PostgreSQL offers excellent performance, especially when dealing with complex queries. Its indexing, optimization, and concurrency control are top-notch.

3. Features: From full-text search to JSON support, Postgres has a wide array of features that can cater to various needs. Its extensibility allows you to add even more functionality through custom plugins.

4. Reliability: It's known for its rock-solid reliability and data integrity. Transactions are ACID-compliant, ensuring that your data remains consistent.

5. Community and Support: With a large and active community, finding help or resources is never a problem. There are plenty of tutorials, forums, and experts ready to assist.

In the upcoming section, we will start by setting up our development environment and then eventually make our way to the most important concepts of PostgreSQL

Installing PostgreSQL and Setting Up Your Environment

Here’s the quickest way to start a working PostgreSQL service on your local computer.

Step 1: Install Docker

Step 2: Copy and paste this code into compose.yaml

version: '3.8' # Use the desired version of Docker Compose

services:
  postgres: # installs PostgreSQL
    image: postgres:latest # Use the latest PostgreSQL image from Docker Hub
    container_name: my-postgres-db # Set the name of the container
    environment:
      POSTGRES_DB: humanresources # Specify the name of the database
      POSTGRES_USER: admin # Set the username for the database
      POSTGRES_PASSWORD: admin#123 # Choose a secure password for the user
    ports:
      - "5432:5432" # Map host port to container port
    volumes:
      - postgres-data:/var/lib/postgresql/data # Mount a volume for persistent data

  pgadmin: #installs pgAdmin
    image: dpage/pgadmin4:latest
    container_name: my-pgadmin
    environment:
      PGADMIN_DEFAULT_EMAIL: myemail@example.com
      PGADMIN_DEFAULT_PASSWORD: mypgadminpassword
    ports:
      - "80:80"
    volumes:
      - pgadmin-data:/var/lib/pgadmin
volumes:
  postgres-data:
  pgadmin-data:

Step 3:

docker compose up

Now open http://localhost/ to connect to your PgAdmin Console

Enter the username and password as per the docker compose file and you are good to go.

Connecting PgAdmin to PostgreSQL DB

1. Access pgAdmin: Open a web browser and navigate to http://localhost. You'll be presented with the pgAdmin login page.

2. Log in to pgAdmin: Use the credentials you set in the PGADMIN_DEFAULT_EMAIL and PGADMIN_DEFAULT_PASSWORD environment variables in the Docker Compose file. Enter the email and password and click "Sign in."

3. Add a PostgreSQL Server: Once logged in, you'll be in the pgAdmin dashboard. To add a PostgreSQL server:

   • Click on "Add New Server" under the "Quick Links" section or navigate to "Servers" in the left sidebar and right-click to choose "Create" > "Server..."

   • In the "General" tab:

     • Enter a name for the server, e.g., "My PostgreSQL Server."

   • In the "Connection" tab:

     • Host name/address: Use the name of the postgres service defined in the Docker Compose file, which is postgres.

     • Port: 5432 (the default PostgreSQL port).

     • Maintenance database: The name of the database you set in the POSTGRES_DB environment variable, which is mydatabase.

     • Username: The username you set in the POSTGRES_USER environment variable, which is myuser.

     • Password: The password you set in the POSTGRES_PASSWORD environment variable, which is mypassword.

   • Click "Save" to add the server.

4. Access the PostgreSQL Server: With the server added, you can expand the server node in the left sidebar to see the databases, schemas, and other database-related information. You can use the pgAdmin interface to interact with the PostgreSQL server, run queries, manage databases, and more.

Docker Compose File: https://github.com/sagars01/postgresql-distilled

Your PgAdmin Dashboard

Once you have logged into the system you should be able to see this dashboard.

This is your home for a couple of days. Here we will manage the entire database and learn the details about it.

The left side is the object explorer and the graphs that you see here are all monitoring your database

For the scope of this post we will not explain at that detail

The basic building blocks of PostgreSQL

Below are some crucial PostgreSQL concepts that provide the foundation for efficient and effective database management in day-to-day usage.

There are thousands of other objects that are useful for PostgreSQL but it is futile to explain all of them.
Learn them from the docs when needed.

1. Service: The Toolbox of Database Management Think of a service as a toolbox that facilitates essential tasks within a database. It handles tasks such as managing connections, running background tasks, and more. This toolbox ensures the smooth functioning of the database, making it an indispensable component.

2. Database: The Heart of Data Storage The database itself is the core repository of data. Within a service, multiple databases can be hosted, each serving distinct purposes. These databases house tables, schemas, and various other components that manage data organization and storage.

3. Schema: Your Data Organizer A schema acts like distinct sections in a notebook, separating and organizing different types of data. This structuring simplifies data management, making it easier to navigate and understand.

4. Catalog: The Ultimate Index Analogous to a book's table of contents, a catalogue serves as a comprehensive index detailing the structure and contents of a database. This allows you to quickly locate and comprehend the data's arrangement.

5. Variable (GUC): Tailoring Database Behavior Global User Configuration (GUC) variables provide a way to modify the database's behaviour. They act as switches, enabling you to customize database settings at the service or database level to match specific requirements.

6. Extension: Enhancing Functionality Extensions serve as a package manager for PostgreSQL, allowing developers to bundle functions, casts, data types, tables, and even GUC variables into a single unit. Extensions enhance database capabilities and promote code sharing.

7. Table: Your Data's Home Tables are perhaps the most fundamental concept in databases. They hold data in a structured format, making it easily accessible and manageable.

8. Foreign Table and FDW: Bridging Data Sources A foreign table, linked via a Foreign Data Wrapper (FDW), lets you incorporate information from external sources as if they were local. This enables querying remote data sources as though they were part of your database.

9. Tablespace: Organizing Storage Consider a tablespace as a designated storage area within a database. It enables you to organize data physically, enhancing performance and maintenance.

10. View: The Insightful Temp Table Think of a view as a dynamic, temporary table crafted to support your primary query. Acting as a logical table, it's constructed using a query. A practical example could involve generating a summarized version of a complex dataset, simplifying data access for your applications.

11. Function: PostgreSQL's Functionality Parallel to JavaScript Functions in PostgreSQL parallel JavaScript functions. They accept inputs and return scalar values or records. These functions play a pivotal role in encapsulating complex logic, enhancing code modularity and reusability.

12. Language: Your Programming Palette Language refers to the programming language used to write functions. PostgreSQL supports multiple languages through extensions. By installing extensions, such as JavaScript or Python, you can enrich your arsenal of function-writing tools.

13. Operator: Your Toolkit's Operators Operators in PostgreSQL mirror those in programming languages. They enable you to perform various operations on data. The versatility extends to creating custom operators tailored to your unique requirements.

14. Data Type: Building Blocks of Data Storage With over 30 built-in data types, including integers, strings, dates, arrays, and specialized types, PostgreSQL provides a diverse toolkit for efficient data storage and manipulation.

15. Cast: Transforming Data Types with Precision Casting enables you to convert data types, ensuring compatibility and facilitating seamless interactions between different data formats.

16. Sequence: The Numeric Auto-Pilot Sequences are PostgreSQL's default primary keys. They automatically generate incrementing numeric values, commonly utilized to create unique identifiers for records.

17. Row or Record: The Essence of Data A record, or row, represents a single unit of data within a table. It's the building block of databases, encapsulating related pieces of information.

18. Trigger: Automated Responses to Events Triggers execute code in response to specific events, such as data modifications. For instance, when new orders are placed, triggers can automatically log these actions in auxiliary tables, like an "order_audit" table.

Bonus: Rules - The Precursor to Triggers Rules, though complex and now not recommended, were a precursor to triggers. They were used to manage custom instructions. While they are part of PostgreSQL's history, current best practices lean towards using triggers for similar functionalities.

Creating Databases, Tables, and Users Using PgAdmin

To create a database you first need to register a server

Establishing your Server Connection

Right-click on the server object that you see on the object explorer

Click Server and register using your docker-compose configuration

Here are my configurations, but feel free to add your own

Now in the connection tab, you will add these credentials that you have configured in your docker-compose file.

Now hit save and you will find your human resources database.

Creating your first table

Now once you expand your Databases object you will find Schema.

Under Schema > public > Tables right click to open the query tool

Once you have opened the query tool paste the SQL Query to create an employee database.

CREATE TABLE employees (
    id VARCHAR(21) PRIMARY KEY,
    first_name VARCHAR(50),
    gender VARCHAR(10),
    start_date DATE,
    last_login_time TIME,
    salary INTEGER,
    bonus_percent DECIMAL(5,3),
    senior_management BOOLEAN,
    team VARCHAR(50),
      employee_id VARCHAR(21)
);

SELECT * FROM employees

Don’t change this query because we will use this table throughout the course.

After executing the above query, you should be able to see the tables and columns have been created

Congratulations! If you have followed everything from the start you're one step ahead to use PostgreSQL

TLDR;

Let’s revisit what we have learned in the entire chapter

1. What is PostgreSQL DB and what makes it special

2. How to setup a local DB with the PgAdmin Management tool

3. What are the building blocks of a PostgreSQL Database

4. How to connect PgAdmin with the PostgreSQL Service and create your first database

What's next?

I am writing this series on PostgreSQL to teach frontend engineers full-stack engineering.

I am a Staff Software Engineer with 10 years of building highly scalable applications.

Follow me on X/ Twitter sagar_codes to learn full-stack engineering.