PostgreSQL Comprehensive Basic Practice

After finishing the first 6 fundamentals lessons, it's time to tie everything together into a complete project—this article walks you through building an online bookstore database from scratch.

1. What You'll Learn

The complete flow from requirements analysis to table design
Create a database and switch connections
Design multiple related tables with constraints
Batch inserts and UPSERT data import
Simple queries to verify data correctness
Modify table structures and clean up

2. A Solo Developer's Real Story

(1) The Pain: Building the Database Solo

Alice is a solo developer who needs to set up the database for an online bookstore project. Her requirements are:

5 core tables: users, books, categories, orders, reviews
User email must be unique; password must not be empty
Book price must be > 0; ISBN must be unique
Orders link users and books
Reviews must have a rating (1–5)
She needs to import a batch of initial data; some books may already exist (requiring UPSERT)

(2) The Complete Database Build Flow

Alice completes the database initialization from scratch with these steps:

SQL

-- Step 1: Create database
CREATE DATABASE bookstore;

-- Step 2: Connect to the new database
\c bookstore

-- Step 3-7: Create tables with proper types and constraints
-- (full SQL below in Section 6)

(3) The Payoff

Full database design experience (from requirements to SQL)
5 tables + constraints + foreign-key cascades = production-grade design
UPSERT batch import = a real-world skill
Done in 30 minutes = the ability to deliver solo

3. Requirements Analysis

(1) Online Bookstore ER Diagram

erDiagram
    USERS ||--o{ ORDERS : places
    BOOKS ||--o{ ORDER_ITEMS : contains
    CATEGORIES ||--o{ BOOKS : has
    USERS ||--o{ REVIEWS : writes
    BOOKS ||--o{ REVIEWS : receives
    ORDERS ||--|| ORDER_ITEMS : includes

    USERS {
        int id PK
        varchar email UK
        varchar name
        varchar password_hash
        timestamptz created_at
    }
    CATEGORIES {
        int id PK
        varchar name UK
        text description
    }
    BOOKS {
        int id PK
        varchar isbn UK
        varchar title
        decimal price
        int stock
        int category_id FK
        jsonb metadata
    }
    ORDERS {
        int id PK
        int user_id FK
        varchar status
        decimal total
        timestamptz created_at
    }
    ORDER_ITEMS {
        int id PK
        int order_id FK
        int book_id FK
        int quantity
        decimal unit_price
    }
    REVIEWS {
        int id PK
        int user_id FK
        int book_id FK
        smallint rating
        text content
        timestamptz created_at
    }

(2) Tables and Constraints List

Table	Columns	Key constraints
users	5	email UNIQUE NOT NULL, password_hash NOT NULL
categories	3	name UNIQUE
books	7	isbn UNIQUE, price > 0 CHECK, stock >= 0 CHECK, category_id FK
orders	5	user_id FK CASCADE, status CHECK, total >= 0 CHECK
order_items	5	order_id FK CASCADE, book_id FK RESTRICT, quantity > 0 CHECK
reviews	6	user_id FK SET NULL, book_id FK CASCADE, rating 1-5 CHECK

4. Build It Step by Step

(1) Create the Database and Connect

▶ Example: Creating the Bookstore Database

SQL

-- Create the bookstore database
CREATE DATABASE bookstore
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8';

-- Connect to the new database
\c bookstore

TEXT

CREATE TABLE

(2) Create the Categories Table

▶ Example: The categories Table

SQL

-- Categories table: book genres
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

-- Insert initial categories
INSERT INTO categories (name, description) VALUES
    ('Programming', 'Books about software development and programming languages'),
    ('Database', 'Database design, SQL, and data management'),
    ('Data Science', 'Machine learning, statistics, and data analysis'),
    ('DevOps', 'CI/CD, cloud computing, and infrastructure'),
    ('Web Development', 'Frontend and backend web technologies');

TEXT

INSERT 0 1

(3) Create the Users Table

▶ Example: The users Table

SQL

-- Users table: bookstore customers
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert sample users
INSERT INTO users (email, name, password_hash) VALUES
    ('alice@example.com', 'Alice', '$2a$12$hash_alice_1234567890abcdefghijklmnopqrs'),
    ('bob@example.com', 'Bob', '$2a$12$hash_bob_1234567890abcdefghijklmnopqrstuvwx'),
    ('charlie@example.com', 'Charlie', '$2a$12$hash_charlie_1234567890abcdefghijklmno')
RETURNING id, email, name;

TEXT

INSERT 0 1

(4) Create the Books Table

▶ Example: The books Table

SQL

-- Books table: the core product
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    isbn VARCHAR(13) UNIQUE NOT NULL,
    title VARCHAR(300) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert sample books with metadata
INSERT INTO books (isbn, title, price, stock, category_id, metadata) VALUES
    ('9780134685991', 'Effective Python', 39.99, 120, 1,
     '{"author": "Brett Slatkin", "pages": 352, "edition": 2}'::jsonb),
    ('9780596007270', 'Learning PostgreSQL', 44.99, 80, 2,
     '{"author": "Regina Obe", "pages": 500}'::jsonb),
    ('9781491910368', 'Python Data Science Handbook', 49.99, 60, 3,
     '{"author": "Jake VanderPlas", "pages": 548}'::jsonb),
    ('9781098118283', 'Kubernetes Up and Running', 54.99, 45, 4,
     '{"author": "Brendan Burns", "pages": 300, "edition": 3}'::jsonb),
    ('9781718500417', 'CSS in Depth', 42.99, 90, 5,
     '{"author": "Keith Grant", "pages": 432}'::jsonb)
RETURNING id, title, price;

TEXT

INSERT 0 1

(5) Create the Orders and Order Items Tables

▶ Example: The orders + order_items Tables

SQL

-- Orders table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(12, 2) DEFAULT 0 CHECK (total_amount >= 0),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

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

-- Insert a sample order
INSERT INTO orders (user_id, status, total_amount) VALUES
    (1, 'paid', 84.98)
RETURNING id;

-- Insert order items (assume order id = 1)
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES
    (1, 1, 1, 39.99),
    (1, 2, 1, 44.99);

TEXT

INSERT 0 1

(6) Create the Reviews Table

▶ Example: The reviews Table

SQL

-- Reviews table: user reviews for books
CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(200),
    content TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insert sample reviews
INSERT INTO reviews (user_id, book_id, rating, title, content) VALUES
    (1, 1, 5, 'Excellent Python Tips',
     'This book covers many practical Python tips that I use daily in my work.'),
    (2, 2, 4, 'Good PostgreSQL Introduction',
     'Great for beginners, but could use more advanced topics like partitioning.'),
    (3, 3, 5, 'Must-have for Data Scientists',
     'Comprehensive coverage of NumPy, Pandas, Matplotlib, and Scikit-learn.')
RETURNING id, rating, title;

TEXT

INSERT 0 1

5. UPSERT Batch Import

▶ Example: Batch Importing Books (some already exist)

SQL

-- Import new batch of books: some already exist (by isbn), some are new
INSERT INTO books (isbn, title, price, stock, category_id, metadata) VALUES
    ('9780134685991', 'Effective Python', 35.99, 150, 1,
     '{"author": "Brett Slatkin", "pages": 352, "edition": 2}'::jsonb),
    ('9780596007270', 'Learning PostgreSQL', 49.99, 100, 2,
     '{"author": "Regina Obe", "pages": 500}'::jsonb),
    ('9781119557265', 'SQL for Data Analysis', 34.99, 200, 2,
     '{"author": "Ulka Rodgers", "pages": 288}'::jsonb),
    ('9781484254555', 'PostgreSQL High Performance', 59.99, 30, 2,
     '{"author": "Ibragimov", "pages": 400}'::jsonb)
ON CONFLICT (isbn)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = books.stock + EXCLUDED.stock
RETURNING id, title,
    CASE WHEN xmax = 0 THEN 'NEW' ELSE 'UPDATED' END AS operation;

TEXT

 id |            title             | operation
----+------------------------------+-----------
  1 | Effective Python             | UPDATED
  2 | Learning PostgreSQL          | UPDATED
  6 | SQL for Data Analysis        | NEW
  7 | PostgreSQL High Performance  | NEW

6. Verification Queries

▶ Example: Data Integrity Verification

SQL

-- 1. Count records in each table
SELECT 'users' AS table_name, COUNT(*) FROM users
UNION ALL SELECT 'categories', COUNT(*) FROM categories
UNION ALL SELECT 'books', COUNT(*) FROM books
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
UNION ALL SELECT 'reviews', COUNT(*) FROM reviews;

-- 2. Verify foreign key relationships
SELECT o.id AS order_id, u.name AS customer,
       b.title, oi.quantity, oi.unit_price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN books b ON oi.book_id = b.id;

-- 3. Verify constraints work (should fail)
-- INSERT INTO books (isbn, title, price, stock) VALUES ('test', 'Test', -10, 5);
-- ERROR: check constraint "books_price_check" violated

-- INSERT INTO reviews (user_id, book_id, rating) VALUES (1, 1, 6);
-- ERROR: check constraint "reviews_rating_check" violated

-- 4. Verify UPSERT results
SELECT isbn, title, price, stock
FROM books
WHERE isbn IN ('9780134685991', '9781119557265')
ORDER BY isbn;

TEXT

 count 
-------
     5
(1 row)

7. Modify the Table Structure

▶ Example: Iterative Improvements with ALTER TABLE

SQL

-- Add a published_year column to books
ALTER TABLE books ADD COLUMN published_year INTEGER;

-- Add an is_featured flag to books
ALTER TABLE books ADD COLUMN is_featured BOOLEAN DEFAULT false;

-- Update published_year from metadata JSONB
UPDATE books
SET published_year = (metadata->>'year')::INTEGER
WHERE metadata ? 'year';

-- Add a phone column to users
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Verify changes
\d books
\d users

TEXT

-- SQL statement executed successfully

8. Complete Example: One-Click Initialization Script

SQL

-- ============================================
-- Complete example: Bookstore database init script
-- Run this from psql connected to postgres DB
-- ============================================

-- 1. Create database
CREATE DATABASE bookstore
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8';

-- 2. Connect to the new database
\c bookstore

-- 3. Create all tables in dependency order
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

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

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    isbn VARCHAR(13) UNIQUE NOT NULL,
    title VARCHAR(300) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(12, 2) DEFAULT 0 CHECK (total_amount >= 0),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price > 0)
);

CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(200),
    content TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 4. Insert sample data
INSERT INTO categories (name, description) VALUES
    ('Programming', 'Software development and programming languages'),
    ('Database', 'Database design, SQL, and data management'),
    ('Data Science', 'Machine learning and data analysis');

INSERT INTO users (email, name, password_hash) VALUES
    ('alice@example.com', 'Alice', '$2a$12$hash_alice'),
    ('bob@example.com', 'Bob', '$2a$12$hash_bob');

INSERT INTO books (isbn, title, price, stock, category_id, metadata) VALUES
    ('9780134685991', 'Effective Python', 39.99, 120, 1,
     '{"author": "Brett Slatkin", "pages": 352}'::jsonb),
    ('9780596007270', 'Learning PostgreSQL', 44.99, 80, 2,
     '{"author": "Regina Obe", "pages": 500}'::jsonb),
    ('9781491910368', 'Python Data Science Handbook', 49.99, 60, 3,
     '{"author": "Jake VanderPlas", "pages": 548}'::jsonb);

INSERT INTO orders (user_id, total_amount) VALUES (1, 84.98);
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES
    (1, 1, 1, 39.99), (1, 2, 1, 44.99);

INSERT INTO reviews (user_id, book_id, rating, title, content) VALUES
    (1, 1, 5, 'Excellent', 'Practical Python tips.'),
    (2, 2, 4, 'Good intro', 'Great for beginners.');

-- 5. Verify
SELECT 'categories' AS t, COUNT(*) FROM categories
UNION ALL SELECT 'users', COUNT(*) FROM users
UNION ALL SELECT 'books', COUNT(*) FROM books
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'reviews', COUNT(*) FROM reviews;

TEXT

     t      | count
------------+-------
 categories |     3
 users      |     2
 books      |     3
 orders     |     1
 reviews    |     2

❓ FAQ

Q Does the order in which tables are created matter?

A Yes, it matters! Because the table a foreign key references must already exist. The correct order is: create the referenced tables first (categories, users), then the tables that reference them (books, orders, reviews), and finally the linking table (order_items).

Q How do I choose between ON DELETE CASCADE and RESTRICT?

A Use CASCADE when child rows should be deleted along with the parent (e.g., deleting a user also deletes their orders). Use RESTRICT when a parent must not be deleted if child rows exist (e.g., a book referenced by order items cannot be deleted). General rule: use CASCADE where the business allows cascading deletes, and RESTRICT where it doesn't.

Q What does xmax = 0 mean in an UPSERT?

A xmax is a PostgreSQL system column that records the transaction ID of the delete/update on that row. An INSERT does not set xmax (value is 0); UPDATE/DELETE do set it. So xmax = 0 means the row was freshly inserted, while xmax != 0 means it was updated. This is a PostgreSQL internal trick.

Q What's the difference between a temporary table (TEMP TABLE) and a normal table?

A A temporary table is visible only in the current session and is dropped automatically when the session ends. It's good for storing intermediate import data or temporary computation results. Advantages: it doesn't pollute the public namespace and requires no manual cleanup.

Q What type is the year field inside the JSONB metadata?

A Numbers inside JSONB are numeric by default. To extract, use ->>'year' (returns TEXT) and then cast with ::INTEGER. If you query by year frequently, add a GENERATED column or an expression index.

Q Can this bookstore database be used in production?

A Structurally yes, but it still lacks several production essentials: 1) index tuning (later lessons); 2) audit fields (updated_at); 3) soft deletes (deleted_at); 4) database user privileges (non-root connections); 5) a backup strategy. Later lessons will add these gradually.

📖 Summary

The database design flow: requirements analysis → ER diagram → create-table order → constraint design → data import → verification
Table creation order follows foreign-key dependencies: referenced tables first, referencing tables later
Constraints protect data quality: UNIQUE (uniqueness) / CHECK (conditional validation) / FK (referential integrity) / NOT NULL (non-empty)
UPSERT batch import: ON CONFLICT DO UPDATE handles duplicate data
The RETURNING clause verifies operation results
ALTER TABLE iteratively improves the table structure (add columns, change constraints)
Comprehensive queries verify data integrity: JOIN to combine tables + COUNT for statistics

📝 Exercises

Basic (★): Following this lesson's steps, create the bookstore database and all 6 tables from scratch, insert the sample data, then run the verification queries to make sure each table's row count matches the examples.
Intermediate (★★): Run an UPSERT on the bookstore database: import 4 book records (2 of which have existing ISBNs). For existing books, update the price and increase the stock; new books insert normally. Use RETURNING to show whether each record is NEW or UPDATED.
Challenge (★★★): Add a book_authors many-to-many linking table to the bookstore database (a book can have multiple authors, and an author can write multiple books), plus an authors table. Design the table structure (with foreign keys and constraints), insert 3 authors and the linking data, then write a JOIN query that shows all author names for each book.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL Comprehensive Basic Practice

1. What You'll Learn

2. A Solo Developer's Real Story

(1) The Pain: Building the Database Solo

(2) The Complete Database Build Flow

(3) The Payoff

3. Requirements Analysis

(1) Online Bookstore ER Diagram

(2) Tables and Constraints List

4. Build It Step by Step

(1) Create the Database and Connect

▶ Example: Creating the Bookstore Database

(2) Create the Categories Table

▶ Example: The categories Table

(3) Create the Users Table

▶ Example: The users Table

(4) Create the Books Table

▶ Example: The books Table

(5) Create the Orders and Order Items Tables

▶ Example: The orders + order_items Tables

(6) Create the Reviews Table

▶ Example: The reviews Table

5. UPSERT Batch Import

▶ Example: Batch Importing Books (some already exist)

6. Verification Queries

▶ Example: Data Integrity Verification

7. Modify the Table Structure

▶ Example: Iterative Improvements with ALTER TABLE

8. Complete Example: One-Click Initialization Script

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好