Learn SQL with PostgreSQL - Prerequisite Skills & Environment Setup

#Introduction

Welcome to the Learn SQL with PostgreSQL series! This series will take you from mastering the SQL language and relational database architecture at zero to production-ready level: starting from prerequisites, RDBMS concept history, database design and normalization, DDL and DML, joins, window functions, transactions and ACID, indexing and query optimization, to security, partitioning, replication, and a production-grade case study. But as the saying goes, "a sturdy house stands on a strong foundation". Before writing your first SELECT query, there are several basic skills and tools that you must have and prepare first.

Episode 0 will be the roadmap to make sure everyone is ready. We'll cover two fundamental skills, prepare all the required software, then get to know psql and GUI clients. Once this episode is done, you'll be fully ready to move on to episode 1.

#Basic Skills You Must Have

Let's start with skills. Without these, no matter how sophisticated your tools are, they won't be useful. Here are the two skill pillars you need to master at least at a basic level.

#Terminal Basics

PostgreSQL was born and grew up in the Linux/Unix ecosystem. Most of its real-world usage — especially on production servers and containers — runs on Linux. Because of that, the ability to operate a terminal through the CLI (Command Line Interface) is a must.

What do you need to master?

1. Navigation & file management. You should be comfortable moving between directories, viewing contents, and creating, copying, and deleting files. This is the most basic skill you'll use every day, including when reading PostgreSQL logs.

2. Running commands with sudo. Installing packages on Linux generally requires sudo. You need to understand the concept of user privileges so you aren't surprised when asked for a password.

3. Environment variables. PostgreSQL uses many environment variables such as PGHOST, PGPORT, and PGDATABASE. Being able to set and read these variables will help you a lot.

Basic terminal commands

pwd              # print the current working directory
ls -la           # list directory contents (including hidden files)
mkdir -p lab-sql # create a project directory
history          # view the history of executed commands

#Understanding Data Storage Concepts

The second skill is conceptual. Before learning SQL, it's good to intuitively understand:

• Data is raw facts: names, numbers, dates, text, and so on.
• Database is a place where data is stored in a structured way so it's easy to find and retrieve later.
• Relational database stores data in tables that relate to each other (we'll break this down thoroughly in episodes 1 and 2).

What you need to understand right now: SQL (Structured Query Language) is the language for "talking to" a database. You don't need to understand how disks work or internal storage mechanics yet — just understand that the queries you write are sent to the server, processed, and the results are returned in the form of rows and columns.

#Software & Tools to Prepare

Now that the skills are covered, it's time to prepare the equipment. Here's the complete list:

#Installing the PostgreSQL Server

There are two main installation paths: via Docker (most recommended for learning because it's fast and doesn't dirty your system) and native installation. Let's start with Docker.

docker run --name postgres \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  -d postgres:16-alpine

The command above will: name the container postgres, set the root password to secret, map port 5432 from the container to the host, and run it in the background using the postgres:16-alpine image.

docker ps
docker logs postgres

For Windows users, the most recommended option for learning is to use WSL2. For Linux users, you can install natively through your package manager:

Native installation on Ubuntu/Debian

sudo apt update
sudo apt install postgresql postgresql-client
sudo systemctl status postgresql

#Verifying the Installation

Once installed, verify that the server and client work. Make sure psql can run and the server accepts connections.

psql --version
pg_isready -h localhost -p 5432

Expected output: psql (PostgreSQL) 16.x for the client version, and localhost:5432 - accepting connections for the server status. If both succeed, your environment is ready.

#Getting to Know psql: PostgreSQL's Official CLI

psql is the main interactive tool for talking to PostgreSQL. It has two types of commands: regular SQL statements and meta-commands that always start with a backslash (\). Here are the meta-commands you should memorize from now on:

First connection to the Docker server:

psql -U postgres -h localhost -p 5432

When asked for a password, enter secret (the password we set earlier). If successful, the prompt changes to postgres=#. Now try your first meta-command:

\l

SELECT version();

#GUI Clients: DBeaver, TablePlus, pgAdmin 4

The psql CLI is powerful, but sometimes we need visualization to understand the database structure. There are three most popular GUI clients:

• pgAdmin 4: built by the PostgreSQL team itself, free, with complete features including a query tool and visual explain plan.
• DBeaver: a universal database tool that supports many engines; free for the community edition.
• TablePlus: lightweight and elegant, popular on macOS, but paid for intensive use.

All these clients connect to the same server with the same parameters: host localhost, port 5432, user postgres, and the password we set during installation. A GUI client doesn't replace psql — they complement each other. CLI for scripting and automation, GUI for visual exploration.

host: localhost
port: 5432
database: postgres
username: postgres
password: secret

#Common Setup Mistakes

Based on experience, these are the three most frequent mistakes when first playing with PostgreSQL:

There's one more that often tricks people: trying to connect without the container running. docker logs postgres is your best friend for checking whether the server is actually alive.

#Closing

In this episode 0 we've built a solid foundation: mastered two fundamental skills (terminal basics and data storage concepts), prepared the required equipment and tools, installed the PostgreSQL server via Docker and natively, verified the installation, learned the psql meta-commands, and gotten to know GUI clients for visual exploration.

Key takeaways:

• PostgreSQL is operated through the terminal and SQL — master the basics of both.
• The fastest installation path for learning is Docker: one command and the server is up.
• psql is your main weapon — memorize the \l, \c, \dt, \d, and \q meta-commands.
• Connection parameters are always the same: host, port, database, username, and password.
• GUI clients are a complement, not a replacement for the CLI.

Make sure you've prepared all the skills and tools above, because the next episode will discuss concepts in more depth. In episode 1, we'll cover the history of the relational data model's birth from Edgar F. Codd, PostgreSQL's evolution from the Ingres project, and the reasons why PostgreSQL has become a modern database choice — from ANSI SQL standard compliance to advanced features like JSONB and MVCC. Stay motivated, because the SQL learning journey has only just begun!