PostgreSQL Intro¶

Postgres has been doing relational databases right since 1996 That's not ancient history - that's two decades of battle-hardened ACID compliance , extensibility , and query optimization that makes every other open-source relational database look like a toddler playing with action figures while Postgres is out here running production systems for hedge funds , governments , and the entire geospatial data infrastructure of the planet

What is PostgreSQL¶

PostgreSQL (pronounced post-gress-Q-L , or just "Postgres" if you want to sound like you've been doing this longer than you have) is an object-relational database management system that supports both SQL and JSON queries , with extensible type systems , custom functions in multiple languages (plpgsql , plpython , plv8) , and indexing methods that range from B-tree to GiST to SP-GiST to BRIN depending on what kind of data you're storing and how you plan to query it

Postgres doesn't need a commercial license, it doesn't phone home, and it runs everywhere from a Raspberry Pi running your home automation to a 64-core EPYC monster serving millions of queries per second for a SaaS platform

Installation¶

# Ubuntu / Debian - the most common production target
sudo apt update && sudo apt install postgresql postgresql-contrib -y
# This installs the server , client tools , and contributed extensions

# macOS - homebrew , because you're developing on a laptop
brew install postgresql@16
brew services start postgresql@16

# Verify it's running
pg_isready
# /var/run/postgresql:5432 - accepting connections

After install, Postgres creates a default user called postgres with no password and a postgres database. First thing you should do is set a password and maybe create a non-root user for your app so you're not running everything as the superuser like a cowboy who's never restored from a backup before

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'your_secure_password';"

psql - the CLI that never lies¶

Stop installing GUI tools for Postgres. dbeaver is fine for visually exploring , but when something breaks in production at 3AM , you don't have a GUI - you have a terminal window and psql. Learn to love it

# Connect to a database
psql -h localhost -U postgres -d mydb
# -h: host , -U: user , -d: database

# Common psql meta-commands (they start with backslash)
\l                    # List all databases
\c mydb               # Connect to mydb
\dt                   # List all tables in current schema
\d users              # Describe table 'users' structure
\di                   # List indexes
\du                   # List users/roles
\conninfo             # Show connection info
\x                    # Toggle expanded display (great for wide tables)
\q                    # Quit (you'd be surprised how many people google this)

Creating databases and tables¶

-- Create a database for your app
CREATE DATABASE myapp;

-- Connect to it (or use \c myapp in psql)
-- Now create a schema (schemas are like namespaces for tables)
CREATE SCHEMA IF NOT EXISTS app;

-- The real deal - creating a proper table with constraints
CREATE TABLE app.users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    -- gen_random_uuid() requires pgcrypto extension:
    -- CREATE EXTENSION IF NOT EXISTS pgcrypto;

    email VARCHAR(255) NOT NULL UNIQUE,
    -- Two constraints: NOT NULL (can't be empty) and UNIQUE (no duplicates)
    -- Uniqueness creates a B-tree index automatically

    username VARCHAR(100) NOT NULL CHECK (char_length(username) >= 3),
    -- CHECK constraint enforces minimum length at database level
    -- Don't rely on application validation alone

    password_hash VARCHAR(255) NOT NULL,
    -- Store hashes , not passwords. bcrypt , argon2 , scrypt
    -- If you're storing plaintext , delete this file and walk away

    role VARCHAR(20) NOT NULL DEFAULT 'user'
        CHECK (role IN ('user', 'admin', 'moderator')),
    -- ENUM-like constraint without the ENUM type baggage

    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_login TIMESTAMPTZ,

    -- Email format validation (basic - regex can't perfectly validate emails)
    CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

Data types that won't bite you¶

Postgres has more data types than you'll ever use , but these are the ones you'll actually touch:

• UUID - universally unique identifiers. Use these for primary keys unless you enjoy guessing game IDs from sequential integers and leaking user counts through incrementing IDs
• VARCHAR(n) and TEXT - strings. TEXT is unlimited. VARCHAR(n) has a cap. Performance is identical so just use TEXT with app-level length validation unless you need the constraint enforced at database level
• INTEGER , BIGINT , NUMERIC(p,s) - integers and decimals. NUMERIC for money (never use FLOAT for money because floating-point rounding will lose you pennies and pennies become dollars and dollars become lawsuits)
• BOOLEAN - true/false. Simple. Use it for flags instead of integer 0/1 magic values
• TIMESTAMPTZ - timestamp with time zone. Always use this over TIMESTAMP because UTC conversion is automatic and you'll never have to ask "is this stored in UTC or server local time?"
• JSONB - binary JSON. Indexable , queryable , and actually performant for semi-structured data. Use this when you need the flexibility of NoSQL within Postgres
• ARRAY - native arrays. Great for tags and simple lists without creating join tables
• ENUM - custom types for values that never change. Use CHECK constraints instead because removing an ENUM value requires altering the type which locks the table

-- Creating a table that uses multiple data types effectively
CREATE TABLE app.orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES app.users(id) ON DELETE CASCADE,
    -- Foreign key: if user is deleted , their orders go too
    -- CASCADE is aggressive - use SET NULL or RESTRICT in production

    items JSONB NOT NULL DEFAULT '[]'::jsonb,
    -- Store order items as JSON array - fast writes , flexible schema

    total_amount NUMERIC(10, 2) NOT NULL CHECK (total_amount > 0),
    -- NUMERIC with precision=10 , scale=2 : up to $99,999,999.99

    status TEXT NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),

    shipping_address TEXT NOT NULL,
    tracking_numbers TEXT[] DEFAULT '{}',  -- Postgres array
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Constraints - your data's immune system¶

Constraints are the database-level rules that prevent garbage from entering your tables regardless of what the application layer does. They're your last line of defense against a developer who forgot validation , a migration that introduced nulls , or an API consumer who sends malformed data

• NOT NULL - column must have a value. No nulls , no excuses
• UNIQUE - all values in this column must be different. Creates an index automatically
• PRIMARY KEY - combines NOT NULL + UNIQUE . Every table should have one. If you don't know which column to use , make a UUID
• FOREIGN KEY - values must exist in another table's column. This is how relationships maintain integrity
• CHECK - any boolean expression that must be true. The most underused constraint. validate emails, ranges, patterns - anything you can express in SQL
• EXCLUSION - ensures no two rows overlap on specific criteria. Advanced but powerful for scheduling and reservations

-- Adding constraints to existing tables
ALTER TABLE app.users
    ADD CONSTRAINT unique_username UNIQUE (username);

ALTER TABLE app.orders
    ADD CONSTRAINT fk_order_user
    FOREIGN KEY (user_id)
    REFERENCES app.users(id)
    ON DELETE RESTRICT;  -- Prevent deletion if orders exist

-- CHECK constraint for complex business rules
ALTER TABLE app.orders
    ADD CONSTRAINT valid_discount
    CHECK (
        (discount_percent IS NULL) OR
        (discount_percent >= 0 AND discount_percent <= 100)
    );

Security footgun - the default postgres user¶

Out of the box, Postgres creates a superuser called postgres with trust authentication (no password) for local socket connections. That means any local user on the machine can connect as the postgres superuser and own your database. On production systems, change the pg_hba.conf authentication method to md5 or scram-sha-256 for local connections and disable trust mode immediately

# Edit pg_hba.conf - typically at /etc/postgresql/16/main/pg_hba.conf
# Change this line from:
# local   all   postgres   peer
# To:
# local   all   postgres   scram-sha-256

# Restart to apply
sudo systemctl restart postgresql

prerequisites¶

A terminal that works and the ability to run sudo or brew without breaking your system. If you've never opened a terminal before, go read the Linux basics first

next → db_02_postgres_crud.md