Example That Covers Many Common Features Of PostgreSQL

Here’s a comprehensive example that covers many common features of PostgreSQL, including:

• Database and table creation
• Data insertion and querying
• Indexing
• Functions and stored procedures
• Triggers
• Views
• Joins
• Common Table Expressions (CTEs)
• Transactions
• Constraints and data types
• JSON data handling

The example will simulate a library system, with books, authors, and borrowers tables. We will use features like indexing, JSON, stored procedures, and more.

1. Creating a Database and Tables

-- Create the database
CREATE DATABASE library_system; -- Connect to the database
\c library_system; -- Create the 'authors' table
CREATE TABLE authors ( author_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, bio TEXT
); -- Create the 'books' table with foreign key referencing 'authors'
CREATE TABLE books ( book_id SERIAL PRIMARY KEY, title VARCHAR(255) NOT NULL, author_id INT REFERENCES authors(author_id) ON DELETE SET NULL, published_date DATE, genre VARCHAR(50), metadata JSONB -- Store additional information like ISBN, language, etc.
); -- Create the 'borrowers' table
CREATE TABLE borrowers ( borrower_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
); -- Create the 'borrowings' table to track which books are borrowed
CREATE TABLE borrowings ( borrowing_id SERIAL PRIMARY KEY, book_id INT REFERENCES books(book_id) ON DELETE CASCADE, borrower_id INT REFERENCES borrowers(borrower_id) ON DELETE CASCADE, borrowed_date DATE NOT NULL DEFAULT CURRENT_DATE, return_date DATE
);

2. Insert Data

-- Insert authors
INSERT INTO authors (name, bio) VALUES
('J.K. Rowling', 'British author, best known for the Harry Potter series.'),
('J.R.R. Tolkien', 'British writer and scholar, author of The Lord of the Rings.'),
('George R.R. Martin', 'American novelist and short story writer, author of A Song of Ice and Fire.'); -- Insert books with metadata stored as JSONB
INSERT INTO books (title, author_id, published_date, genre, metadata) VALUES
('Harry Potter and the Philosopher''s Stone', 1, '1997-06-26', 'Fantasy', '{"ISBN": "9780747532699", "language": "English"}'),
('The Hobbit', 2, '1937-09-21', 'Fantasy', '{"ISBN": "9780618260300", "language": "English"}'),
('A Game of Thrones', 3, '1996-08-06', 'Fantasy', '{"ISBN": "9780553103540", "language": "English"}'); -- Insert borrowers
INSERT INTO borrowers (name, email) VALUES
('Alice Johnson', 'alice@example.com'),
('Bob Smith', 'bob@example.com');

3. Querying Data

-- Get all books with their authors' names
SELECT b.title, a.name AS author, b.published_date, b.genre
FROM books b
JOIN authors a ON b.author_id = a.author_id; -- Get all books borrowed by Alice
SELECT b.title, bo.name AS borrower, br.borrowed_date, br.return_date
FROM borrowings br
JOIN books b ON br.book_id = b.book_id
JOIN borrowers bo ON br.borrower_id = bo.borrower_id
WHERE bo.name = 'Alice Johnson';

4. Creating Indexes

-- Create an index on the 'published_date' of the 'books' table to speed up date-based searches
CREATE INDEX idx_books_published_date ON books(published_date); -- Create a full-text index for searching books by title
CREATE INDEX idx_books_title ON books USING gin (to_tsvector('english', title)); -- Query using full-text search
SELECT * FROM books
WHERE to_tsvector('english', title) @@ to_tsquery('Hobbit');

5. Creating Functions and Stored Procedures

-- Function to calculate the number of books borrowed by a borrower
CREATE OR REPLACE FUNCTION get_books_borrowed_count(borrower_id INT) RETURNS INT AS $$
BEGIN RETURN (SELECT COUNT(*) FROM borrowings WHERE borrower_id = $1);
END;
$$ LANGUAGE plpgsql; -- Use the function
SELECT get_books_borrowed_count(1); -- Get number of books borrowed by borrower with ID 1

6. Using Triggers

-- Create a trigger function to log borrowing activity
CREATE OR REPLACE FUNCTION log_borrowing() RETURNS TRIGGER AS $$
BEGIN INSERT INTO borrowing_logs (borrower_id, book_id, action, action_date) VALUES (NEW.borrower_id, NEW.book_id, 'borrowed', NOW()); RETURN NEW;
END;
$$ LANGUAGE plpgsql; -- Create the borrowing_logs table
CREATE TABLE borrowing_logs ( log_id SERIAL PRIMARY KEY, borrower_id INT, book_id INT, action VARCHAR(50), action_date TIMESTAMP
); -- Create a trigger on the 'borrowings' table
CREATE TRIGGER after_borrow
AFTER INSERT ON borrowings
FOR EACH ROW EXECUTE FUNCTION log_borrowing();

7. Using Views

-- Create a view to simplify querying available books
CREATE VIEW available_books AS
SELECT b.book_id, b.title, a.name AS author, b.published_date, b.genre
FROM books b
JOIN authors a ON b.author_id = a.author_id
WHERE b.book_id NOT IN (SELECT book_id FROM borrowings WHERE return_date IS NULL); -- Query the view to get all available books
SELECT * FROM available_books;

8. Using Common Table Expressions (CTEs)

-- Use a CTE to get the most borrowed books
WITH borrowed_counts AS ( SELECT book_id, COUNT(*) AS borrow_count FROM borrowings GROUP BY book_id
)
SELECT b.title, a.name AS author, bc.borrow_count
FROM borrowed_counts bc
JOIN books b ON bc.book_id = b.book_id
JOIN authors a ON b.author_id = a.author_id
ORDER BY bc.borrow_count DESC;

9. Handling JSON Data

-- Retrieve metadata from a JSONB field
SELECT title, metadata->>'ISBN' AS isbn, metadata->>'language' AS language
FROM books
WHERE metadata->>'language' = 'English'; -- Update JSONB data to add a new field 'edition'
UPDATE books
SET metadata = jsonb_set(metadata, '{edition}', '"First Edition"')
WHERE title = 'The Hobbit';

10. Working with Transactions

-- Start a transaction to ensure atomicity of multiple operations
BEGIN; -- Insert a new book and borrow it in a single transaction
INSERT INTO books (title, author_id, published_date, genre, metadata)
VALUES ('The Silmarillion', 2, '1977-09-15', 'Fantasy', '{"ISBN": "9780618391110", "language": "English"}')
RETURNING book_id; -- Assume the returned book_id is 4
INSERT INTO borrowings (book_id, borrower_id) VALUES (4, 1); -- Commit the transaction to make changes permanent
COMMIT;

11. Constraints and Data Types

-- Add a NOT NULL constraint to an existing column
ALTER TABLE books ALTER COLUMN genre SET NOT NULL; -- Add a UNIQUE constraint to ensure no duplicate titles in 'books'
ALTER TABLE books ADD CONSTRAINT unique_book_title UNIQUE (title); -- Change data type of 'published_date' if needed
ALTER TABLE books ALTER COLUMN published_date TYPE TIMESTAMP;

Summary of Features Covered:

• Database and Table Creation: Creating a library system with multiple related tables.
• Data Insertion and Querying: Basic and complex queries using joins and conditions.
• Indexing: Speeding up searches with indexes on dates and full-text fields.
• Functions and Stored Procedures: Calculating borrow counts with user-defined functions.
• Triggers: Logging activities using triggers.
• Views: Simplifying data access with views.
• CTEs: Using CTEs for organized queries.
• Transactions: Ensuring consistency with transactions.
• Constraints: Applying data constraints like NOT NULL and UNIQUE.
• JSON Handling: Working with JSONB data type.

This example covers a wide range of PostgreSQL functionalities, offering a strong foundation for understanding the power and flexibility of the database.