Getting Started with PostgreSQL
The basic building blocks that power the best database of the planet
Table of contents
- Introduction to PostgreSQL and Its Significance
- Installing PostgreSQL and Setting Up Your Environment
- The basic building blocks of PostgreSQL
- Creating Databases, Tables, and Users Using PgAdmin
- What's next?
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:
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!
Performance: PostgreSQL offers excellent performance, especially when dealing with complex queries. Its indexing, optimization, and concurrency control are top-notch.
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.
Reliability: It's known for its rock-solid reliability and data integrity. Transactions are ACID-compliant, ensuring that your data remains consistent.
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
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
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
- "5432:5432" # Map host port to container port
- postgres-data:/var/lib/postgresql/data # Mount a volume for persistent data
pgadmin: #installs pgAdmin
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
Access pgAdmin: Open a web browser and navigate to
http://localhost. You'll be presented with the pgAdmin login page.
Log in to pgAdmin: Use the credentials you set in the
PGADMIN_DEFAULT_PASSWORDenvironment variables in the Docker Compose file. Enter the email and password and click "Sign in."
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
postgresservice defined in the Docker Compose file, which is
Port: 5432 (the default PostgreSQL port).
Maintenance database: The name of the database you set in the
POSTGRES_DBenvironment variable, which is
Username: The username you set in the
POSTGRES_USERenvironment variable, which is
Password: The password you set in the
POSTGRES_PASSWORDenvironment variable, which is
Click "Save" to add the server.
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.
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,
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
Let’s revisit what we have learned in the entire chapter
What is PostgreSQL DB and what makes it special
How to setup a local DB with the PgAdmin Management tool
What are the building blocks of a PostgreSQL Database
How to connect PgAdmin with the PostgreSQL Service and create your first database
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.
Did you find this article valuable?
Support Sagar by becoming a sponsor. Any amount is appreciated!