Why PostgreSQL advanced features matter - See It in Action

Why PostgreSQL Advanced Features Matter

📖 Scenario: You are working for a small online bookstore. You want to organize your book data efficiently and use some advanced PostgreSQL features to make your database smarter and faster.

🎯 Goal: Build a simple PostgreSQL database table for books, add a configuration for a price threshold, write a query to filter books by price above the threshold, and complete the setup with an index to speed up queries.

📋 What You'll Learn

Create a table named books with columns id (integer), title (text), and price (numeric).

Add a variable price_limit to set a price threshold.

Write a query to select books priced above price_limit (20).

Create an index on the price column to optimize queries.

💡 Why This Matters

🌍 Real World

Online stores and businesses use PostgreSQL advanced features to handle large data efficiently and make queries faster.

💼 Career

Database administrators and backend developers use these features to optimize database performance and write smarter queries.

1

Create the books table

Write a SQL statement to create a table called books with columns id as integer, title as text, and price as numeric.

PostgreSQL

-- Your code here

Need a hint?

Use CREATE TABLE followed by the table name and define each column with its data type.

2

Set a price threshold variable

Declare a variable called price_limit and set it to 20. Use the DO block with DECLARE to define this variable in PostgreSQL.

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    title TEXT,
    price NUMERIC
);

-- Your code here

Need a hint?

Use a DO block with DECLARE to create variables in PostgreSQL procedural code.

3

Query books priced above the threshold

Write a SQL query that selects title and price from books where the price is greater than 20 (the price_limit).

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    title TEXT,
    price NUMERIC
);

DO $$
DECLARE
    price_limit NUMERIC := 20;
BEGIN
    NULL; -- placeholder
END $$;

-- Your code here

Need a hint?

Use a simple SELECT with a WHERE clause to filter books by price. For advanced features, you can also explore FILTER or window functions later.

4

Create an index on the price column

Write a SQL statement to create an index named idx_price on the price column of the books table to speed up price queries.

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    title TEXT,
    price NUMERIC
);

DO $$
DECLARE
    price_limit NUMERIC := 20;
BEGIN
    NULL; -- placeholder
END $$;

SELECT title, price
FROM books
WHERE price > 20;

-- Your code here

Need a hint?

Use CREATE INDEX followed by the index name and the table and column to index.