PostgreSQL Zero to Advanced #1: Introduction & Setup

Welcome to PostgreSQL Zero to Advanced, a 10-part series that takes you from "what is a database?" all the way to window functions, JSONB and production-grade permissions. Every part builds on the same sample project: a small online course platform with users, courses and orders. By the end you will have written every query yourself and seen exactly what PostgreSQL returns.

In this first part we keep things simple: understand what a relational database is, install PostgreSQL, and learn to move around with psql, the command-line client you will use throughout the series.

What is a relational database?

A relational database stores data in tables. A table looks like a spreadsheet: every column has a name and a data type, and every row is one record. The "relational" part means tables can reference each other. In our course platform, an orders row will point to a row in users (who bought) and a row in courses (what they bought).

You talk to a relational database with SQL (Structured Query Language). Instead of telling the database how to find data, you describe what you want, and the database figures out the fastest way to get it.

Why PostgreSQL in particular?

• Open source and free, with a very active community and a release every year.
• Strict and reliable: full ACID transactions, strong constraints and excellent data integrity.
• Feature-rich: JSONB, full-text search, window functions, arrays, custom types and extensions such as PostGIS.
• Widely supported: every major cloud offers a managed PostgreSQL, and nearly every framework (Prisma, Drizzle, Django, Rails, Laravel) speaks it natively.

Installing PostgreSQL

Pick whichever option matches your machine. This series uses PostgreSQL 17, but everything works on version 13 or newer.

macOS (Homebrew)

brew install postgresql@17
brew services start postgresql@17

Ubuntu / Debian

sudo apt update
sudo apt install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql

Docker (any OS)

Docker is the cleanest option if you do not want to install anything globally. Never type the password directly into the command; read it from an environment variable instead.

# TODO: Securely load this value from an environment variable or secrets vault. Do not hardcode.
export POSTGRES_PASSWORD="$(cat ~/.config/pg-course/password)"

docker run --name pg-course \
  -e POSTGRES_PASSWORD="$POSTGRES_PASSWORD" \
  -p 127.0.0.1:5432:5432 \
  -d postgres:17

Binding to 127.0.0.1 makes sure the database is only reachable from your own machine.

Connecting with psql

psql is the official interactive terminal for PostgreSQL. Connect to the local server as the default postgres superuser:

psql -h localhost -U postgres

On Linux you may need sudo -u postgres psql instead. Once connected you will see a prompt like this:

psql (17.6)
Type "help" for help.

postgres=#

The text before =# is the database you are currently connected to. Let's check the server version and run our very first query:

SHOW server_version;

Output:

 server_version  
-----------------
 17.6 (Homebrew)
(1 row)

SELECT 'Hello, PostgreSQL!' AS greeting, 1 + 1 AS result;

Output:

      greeting      | result 
--------------------+--------
 Hello, PostgreSQL! |      2
(1 row)

Notice two things: every SQL statement ends with a semicolon, and psql always shows the column headers, a separator line and the number of rows returned. You will see this format in every part of the series.

Essential psql meta-commands

Commands that start with a backslash are meta-commands. They are handled by psql itself, not by the SQL engine, so they do not need a semicolon.

Command	What it does
\l	List all databases
\c dbname	Connect to another database
\dt	List tables in the current database
\d table	Describe a table (columns, indexes, constraints)
\conninfo	Show the current connection
\x	Toggle expanded output (useful for wide rows)
\? / \h	Help for meta-commands / SQL commands
\q	Quit psql

Creating the course_platform database

The server ships with a database called postgres, but you should never build an application inside it. Let's create a dedicated database for our project:

CREATE DATABASE course_platform;

Output:

CREATE DATABASE

Now list all databases with \l:

\l

Output:

                                                        List of databases
      Name       |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges   
-----------------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
 course_platform | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | 
 postgres        | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | 
 template0       | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
                 |          |          |                 |             |             |        |           | postgres=CTc/postgres
 template1       | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
                 |          |          |                 |             |             |        |           | postgres=CTc/postgres
(4 rows)

template0 and template1 are templates PostgreSQL copies when you run CREATE DATABASE. Leave them alone. Next, switch to our new database with \c:

\c course_platform

Output:

You are now connected to database "course_platform" as user "postgres".

The prompt changes to course_platform=#. You can confirm where you are with \conninfo:

You are connected to database "course_platform" as user "postgres" on host "localhost" (address "127.0.0.1") at port "5432".

The database is empty, so \dt has nothing to show yet:

\dt

Output:

Did not find any relations.

A quick test drive

Let's create a throwaway table, insert two rows and read them back. Do not worry about the syntax yet; Part 2 and Part 3 explain every keyword.

CREATE TABLE greetings (
    id      INTEGER,
    message TEXT
);

INSERT INTO greetings (id, message)
VALUES (1, 'Halo'), (2, 'Hello');

SELECT * FROM greetings;

Output:

CREATE TABLE
INSERT 0 2
 id | message 
----+---------
  1 | Halo
  2 | Hello
(2 rows)

Now \dt finds our table:

\dt

Output:

           List of relations
 Schema |   Name    | Type  |  Owner   
--------+-----------+-------+----------
 public | greetings | table | postgres
(1 row)

We do not need this table for the rest of the series, so drop it:

DROP TABLE greetings;

Output:

DROP TABLE

Common mistakes

• Forgetting the semicolon. If the prompt changes from =# to -#, psql is waiting for you to finish the statement. Type ; and press Enter.
• Using a semicolon after a meta-command. \dt; usually still works, but \c course_platform; tries to connect to a database literally named with the semicolon. Keep meta-commands clean.
• Building your app in the postgres database. Always create a dedicated database per project.
• Exposing the port publicly. For local development bind to 127.0.0.1 and keep passwords in environment variables or a secrets manager, never in scripts committed to Git.
• Mixing up quotes. Single quotes are for string values ('Halo'); double quotes are for identifiers ("MyTable").

Exercise

1. Connect with psql and run SELECT current_date, current_user;.
2. Create a database called playground, connect to it with \c, then go back to course_platform.
3. Run \? and find the command that toggles query timing (hint: it starts with \t).
4. Drop the playground database with DROP DATABASE playground; (you cannot drop the database you are currently connected to).

Summary

You now know what a relational database is, why PostgreSQL is a great choice, how to install it, and how to navigate with psql using \l, \c, \dt and friends. You also created the course_platform database that the whole series will use.

In Part 2 we will design the real tables (users, courses and orders), choose the right data types and let constraints guard our data.