PostgreSQL Introduction - Relational Database Basics | Tutorials

What You’ll Learn in This Tutorial

✓ PostgreSQL setup
✓ Basic SQL statements (SELECT, INSERT, UPDATE, DELETE)
✓ Table creation and relations
✓ Indexes and performance
✓ Transactions
✓ Practical query patterns

Prerequisites

Basic command line operations
Basic programming knowledge

Step 1: PostgreSQL Setup

Installation

# macOS (Homebrew)
brew install postgresql@16
brew services start postgresql@16

# Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

# Windows
# Download installer from PostgreSQL official site

Connection Check

# Connect to PostgreSQL
psql -U postgres

# Check version
SELECT version();

# Exit
\q

Creating a Database

-- Create database
CREATE DATABASE tutorial_db;

-- List databases
\l

-- Connect to database
\c tutorial_db

-- Check current database
SELECT current_database();

Step 2: Table Creation and Basic Operations

Creating Tables

-- Users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Orders table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Order items table
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL
);

Checking Tables

-- List tables
\dt

-- Check table structure
\d users
\d+ products

-- Detailed information
SELECT
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_name = 'users';

Step 3: Inserting Data (INSERT)

Basic INSERT

-- Insert single row
INSERT INTO users (username, email, password_hash, full_name)
VALUES ('john', 'john@example.com', 'hash123', 'John Doe');

-- Insert multiple rows
INSERT INTO users (username, email, password_hash, full_name)
VALUES
    ('jane', 'jane@example.com', 'hash456', 'Jane Smith'),
    ('bob', 'bob@example.com', 'hash789', 'Bob Wilson'),
    ('alice', 'alice@example.com', 'hash012', 'Alice Brown');

-- Insert product data
INSERT INTO products (name, description, price, stock, category)
VALUES
    ('Laptop', 'High-performance laptop', 980, 50, 'electronics'),
    ('Mouse', 'Wireless mouse', 35, 200, 'electronics'),
    ('Keyboard', 'Mechanical keyboard', 120, 80, 'electronics'),
    ('T-shirt', '100% cotton', 25, 150, 'clothing'),
    ('Jeans', 'Slim fit', 80, 60, 'clothing');

RETURNING Clause

-- Get inserted data
INSERT INTO users (username, email, password_hash)
VALUES ('newuser', 'newuser@example.com', 'newhash')
RETURNING id, username, created_at;

Step 4: Retrieving Data (SELECT)

Basic SELECT

-- Get all columns
SELECT * FROM users;

-- Get specific columns
SELECT id, username, email FROM users;

-- Conditional retrieval
SELECT * FROM users WHERE is_active = true;

-- Multiple conditions
SELECT * FROM products
WHERE category = 'electronics' AND price < 100;

-- OR condition
SELECT * FROM products
WHERE category = 'electronics' OR category = 'clothing';

-- IN clause
SELECT * FROM products
WHERE category IN ('electronics', 'clothing');

-- LIKE (partial match)
SELECT * FROM users WHERE email LIKE '%@example.com';

-- NULL check
SELECT * FROM users WHERE full_name IS NOT NULL;

Sorting and Limits

-- Sort (ascending)
SELECT * FROM products ORDER BY price ASC;

-- Sort (descending)
SELECT * FROM products ORDER BY price DESC;

-- Sort by multiple columns
SELECT * FROM products ORDER BY category ASC, price DESC;

-- Limit rows
SELECT * FROM products LIMIT 10;

-- With offset (pagination)
SELECT * FROM products
ORDER BY id
LIMIT 10 OFFSET 20;  -- 10 rows starting from row 21

Aggregate Functions

-- Count
SELECT COUNT(*) FROM users;

-- Sum
SELECT SUM(price * stock) as total_value FROM products;

-- Average
SELECT AVG(price) as average_price FROM products;

-- Max/Min
SELECT MAX(price), MIN(price) FROM products;

-- Group by
SELECT category, COUNT(*) as count, AVG(price) as avg_price
FROM products
GROUP BY category;

-- HAVING (filter groups)
SELECT category, COUNT(*) as count
FROM products
GROUP BY category
HAVING COUNT(*) >= 2;

Step 5: Updating and Deleting Data

UPDATE

-- Update single row
UPDATE users
SET full_name = 'John Doe (Updated)', updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- Update multiple rows
UPDATE products
SET price = price * 0.9  -- 10% off
WHERE category = 'electronics';

-- RETURNING clause
UPDATE products
SET stock = stock - 1
WHERE id = 1
RETURNING id, name, stock;

DELETE

-- Conditional delete
DELETE FROM users WHERE id = 5;

-- Delete multiple rows
DELETE FROM products WHERE stock = 0;

-- Delete all (caution)
-- DELETE FROM products;

-- TRUNCATE for fast deletion
-- TRUNCATE TABLE products;

Step 6: JOIN for Table Combination

Test Data Preparation

-- Order data
INSERT INTO orders (user_id, total_amount, status)
VALUES
    (1, 1015, 'completed'),
    (2, 155, 'pending'),
    (1, 80, 'completed');

-- Order item data
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
    (1, 1, 1, 980),
    (1, 2, 1, 35),
    (2, 2, 2, 35),
    (2, 3, 1, 120),
    (3, 5, 1, 80);

INNER JOIN

-- Join users and orders
SELECT
    u.username,
    o.id as order_id,
    o.total_amount,
    o.status,
    o.ordered_at
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

LEFT JOIN

-- All users with orders (including users without orders)
SELECT
    u.username,
    COUNT(o.id) as order_count,
    COALESCE(SUM(o.total_amount), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

Multiple Table JOIN

-- Order details (join users, orders, products)
SELECT
    u.username,
    o.id as order_id,
    p.name as product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) as subtotal
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON oi.product_id = p.id
ORDER BY o.id, oi.id;

Step 7: Subqueries and CTE

Subqueries

-- Subquery in WHERE
SELECT * FROM users
WHERE id IN (
    SELECT DISTINCT user_id FROM orders
);

-- Products above average price
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Subquery in FROM
SELECT category, avg_price
FROM (
    SELECT category, AVG(price) as avg_price
    FROM products
    GROUP BY category
) as category_stats
WHERE avg_price > 50;

CTE (Common Table Expression)

-- Readable query with WITH clause
WITH user_orders AS (
    SELECT
        user_id,
        COUNT(*) as order_count,
        SUM(total_amount) as total_amount
    FROM orders
    GROUP BY user_id
)
SELECT
    u.username,
    u.email,
    uo.order_count,
    uo.total_amount
FROM users u
INNER JOIN user_orders uo ON u.id = uo.user_id
ORDER BY uo.total_amount DESC;

Step 8: Indexes

Creating Indexes

-- Single column index
CREATE INDEX idx_products_category ON products(category);

-- Compound index
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Partial index
CREATE INDEX idx_active_users ON users(username) WHERE is_active = true;

Checking Indexes

-- List indexes for table
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'products';

-- Check query execution plan
EXPLAIN SELECT * FROM products WHERE category = 'electronics';

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

Step 9: Transactions

Basic Transaction

-- Start transaction
BEGIN;

-- Decrease stock and create order
UPDATE products SET stock = stock - 1 WHERE id = 1;
INSERT INTO orders (user_id, total_amount) VALUES (1, 980);

-- Commit if no problems
COMMIT;

-- Rollback if problems
-- ROLLBACK;

Best Practices

1. Index Design
   - Index columns used in WHERE and JOIN
   - Be conservative with frequently updated tables
   - Verify effectiveness with EXPLAIN

2. Query Optimization
   - Avoid SELECT *
   - Only retrieve needed columns
   - Use JOIN instead of N+1 queries

3. Transactions
   - Keep them short
   - Choose appropriate isolation level
   - Watch out for deadlocks

4. Security
   - Use parameterized queries
   - Principle of least privilege
   - Encrypt sensitive data

Summary

PostgreSQL is a powerful relational database. Mastering basic CRUD operations, JOINs, and transactions will enable you to handle data management for many applications. Index design for performance is also important.

← Back to list