Exercises for PostgreSQL Schema in pgAdmin

Prerequisites

• Ensure pgAdmin is installed and connected to a PostgreSQL server.

• Create a test database (pg_schema_test) to perform exercises.

---

1. Aggregates

🔹 Exercise 1.1: Create a Custom Aggregate Function

1. Navigate to Aggregates under the public schema.

2. Create an aggregate function sum_of_squares that calculates the sum of squares of numeric values.

3. Test the aggregate function using the SELECT statement.

SELECT sum_of_squares(value) FROM (VALUES (2), (3), (4)) AS t(value);

🔹 Exercise 1.2: Modify an Aggregate

• Edit the previously created aggregate and change the function it references.

---

2. Collations

🔹 Exercise 2.1: Create a Custom Collation

1. Navigate to Collations under the public schema.

2. Create a collation for French language sorting.

3. Create a test table and verify sorting behavior.

CREATE TABLE test_collation (word TEXT COLLATE "fr_FR");
INSERT INTO test_collation VALUES ('école'), ('étudiant'), ('ami'), ('zoo');
SELECT * FROM test_collation ORDER BY word;

---

3. Domains

🔹 Exercise 3.1: Create a Domain for Email Validation

1. Navigate to Domains under the public schema.

2. Create a domain email_domain based on TEXT with a constraint enforcing email format.

3. Test with valid and invalid emails.

CREATE DOMAIN email_domain AS TEXT CHECK (value ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE TABLE users (id SERIAL PRIMARY KEY, email email_domain);

INSERT INTO users (email) VALUES ('valid@example.com');  -- Should work
INSERT INTO users (email) VALUES ('invalid_email');      -- Should fail

---

4. Full-Text Search (FTS) Configurations

🔹 Exercise 4.1: Configure Full-Text Search

1. Create an FTS configuration for English (pg_catalog.english).

2. Test full-text search on sample text.

SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
SELECT to_tsquery('english', 'quick & fox');

---

5. Foreign Tables

🔹 Exercise 5.1: Create a Foreign Table

1. Create a foreign server using postgres_fdw.

2. Create a foreign table that retrieves data from another PostgreSQL database.

CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'remote_db');
CREATE USER MAPPING FOR postgres SERVER foreign_server OPTIONS (user 'remote_user', password 'remote_pass');
CREATE FOREIGN TABLE foreign_users (
    id INT,
    name TEXT
) SERVER foreign_server OPTIONS (schema_name 'public', table_name 'users');

SELECT * FROM foreign_users;

---

6. Functions

🔹 Exercise 6.1: Create a Simple Function

• Create a function that calculates the area of a rectangle.

CREATE FUNCTION rectangle_area(length DOUBLE PRECISION, width DOUBLE PRECISION)
RETURNS DOUBLE PRECISION AS $$
BEGIN
    RETURN length * width;
END;
$$ LANGUAGE plpgsql;

SELECT rectangle_area(5, 10);

---

7. Materialized Views

🔹 Exercise 7.1: Create and Refresh a Materialized View

1. Create a materialized view based on an existing table.

2. Update the table and refresh the materialized view.

CREATE MATERIALIZED VIEW user_count AS 
SELECT COUNT(*) AS total_users FROM users;

REFRESH MATERIALIZED VIEW user_count;

---

8. Sequences

🔹 Exercise 8.1: Create a Custom Sequence

1. Create a sequence order_seq that starts from 1000.

2. Use it in a table for auto-incrementing IDs.

CREATE SEQUENCE order_seq START 1000;
CREATE TABLE orders (
    id INT DEFAULT nextval('order_seq'),
    product_name TEXT
);

INSERT INTO orders (product_name) VALUES ('Laptop'), ('Phone');
SELECT * FROM orders;

---

9. Trigger Functions

🔹 Exercise 9.1: Create a Trigger for Automatic Timestamping

1. Create a table with a timestamp column.

2. Create a trigger that automatically updates updated_at before any row modification.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    updated_at TIMESTAMP DEFAULT now()
);

CREATE FUNCTION update_timestamp() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER product_update BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION update_timestamp();

INSERT INTO products (name) VALUES ('Tablet');
UPDATE products SET name = 'Smartphone' WHERE id = 1;
SELECT * FROM products;

---

10. Views

🔹 Exercise 10.1: Create a View for Active Users

• Create a view that filters users who logged in within the last 30 days.

CREATE VIEW active_users AS 
SELECT id, name, last_login 
FROM users 
WHERE last_login > NOW() - INTERVAL '30 days';

SELECT * FROM active_users;

---

Bonus Challenge

💡 Build a Comprehensive Schema

• Design a social media schema in pgAdmin including:

  • Users Table

  • Posts Table

  • Comments Table

  • Likes Table

  • A full-text search index for posts

  • A trigger that updates the updated_at field on posts

  • A function that counts user comments

  • A materialized view for trending posts

---

Final Thoughts

These exercises offer a progressive approach to understanding PostgreSQL schema items in pgAdmin. They help explore practical use cases such as data integrity (Domains, Triggers), performance optimization (Materialized Views, Indexing), and data accessibility (Foreign Tables, Aggregates).