PostgreSQL Tutorial for Beginners

Introduction

PostgreSQL is one of the most popular open-source relational databases, used by everything from small side projects to companies like Instagram and Spotify. It's powerful, free, and follows SQL standards closely, which makes it a great database to learn first.

In this guide, we'll cover everything a beginner needs:

1. Installing PostgreSQL
2. Creating databases and tables
3. Inserting and querying data
4. Updating and deleting rows
5. Joins between tables
6. Indexes for performance

Every example uses a simple users and orders schema you can follow along with.

1. Installing PostgreSQL

On macOS, the easiest way is Homebrew:

brew install postgresql@16
brew services start postgresql@16

On Ubuntu/Debian:

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

Verify it's running and open the interactive shell:

psql postgres

2. Creating a Database and Table

Inside psql, create a database and switch to it:

CREATE DATABASE shop;
\c shop

Now create your first table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

SERIAL auto-increments the id, and UNIQUE prevents duplicate emails. Check the table structure with:

\d users

3. Inserting and Querying Data

Insert a few rows:

INSERT INTO users (name, email)
VALUES
    ('Ali Khan', 'ali@example.com'),
    ('Sara Ahmed', 'sara@example.com'),
    ('Bilal Tariq', 'bilal@example.com');

Query the data back:

SELECT * FROM users;

SELECT name, email FROM users
WHERE created_at > NOW() - INTERVAL '7 days';

SELECT * FROM users
ORDER BY name ASC
LIMIT 2;

WHERE filters rows, ORDER BY sorts results, and LIMIT caps how many rows come back.

4. Updating and Deleting Rows

Update a row by its unique identifier — always filter with WHERE, or you'll update every row:

UPDATE users
SET name = 'Ali Hassan Khan'
WHERE id = 1;

Delete a row the same way:

DELETE FROM users
WHERE email = 'bilal@example.com';

If you skip WHERE on a DELETE or UPDATE, it applies to the entire table — always double-check before running it.

5. Joins Between Tables

Real apps need related data across tables. Create an orders table that references users:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    product VARCHAR(100) NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    ordered_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO orders (user_id, product, amount)
VALUES
    (1, 'Laptop', 1200.00),
    (1, 'Mouse', 25.00),
    (2, 'Keyboard', 60.00);

Use JOIN to combine rows from both tables:

SELECT users.name, orders.product, orders.amount
FROM orders
JOIN users ON orders.user_id = users.id;

Get the total spent per user with a LEFT JOIN and aggregation:

SELECT users.name, COALESCE(SUM(orders.amount), 0) AS total_spent
FROM users
LEFT JOIN orders ON orders.user_id = users.id
GROUP BY users.name
ORDER BY total_spent DESC;

6. Indexes for Performance

As tables grow, queries that filter on a column without an index get slow. Add one on columns you query often:

CREATE INDEX idx_orders_user_id ON orders(user_id);

Check whether a query uses the index with EXPLAIN:

EXPLAIN SELECT * FROM orders WHERE user_id = 1;

Don't over-index — every index speeds up reads but slows down writes, since PostgreSQL has to update the index on every INSERT or UPDATE. Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses, not on every column.

Putting It All Together

That's the core of PostgreSQL — enough to build a real backend for any app. From here, the best next step is connecting it to a backend framework like Django or Express and building actual features on top of it.