postgresql_reference.sql

-- --------------------------------------------------------------------------------
-- PostgreSQL Reference and Guide

-- ReferenceCollection.com
-- Licensed under CC BY-SA
-- --------------------------------------------------------------------------------

-- Table of Contents
--------------------
-- 1.  Introduction
-- 2.  Basic Syntax
-- 3.  Data Types
-- 4.  Database Operations
-- 5.  Table Operations
-- 6.  Constraints
-- 7.  Data Manipulation
-- 8.  Querying Data 
-- 9.  Joins
-- 10. Aggregate Functions
-- 11. Subqueries
-- 12. Indexes
-- 13. Stored Procedures and Functions
-- 14. Transactions
-- 15. Views
-- 16. Triggers
-- 17. User Management
-- 18. Backup and Restore
-- 19. Concurrency Control
-- 20. Replication
-- 21. Partitioning
-- 22. Extensions
-- 23. JSON and JSONB
-- 24. Full-Text Search
-- 25. Window Functions
-- 26. Common Table Expressions (CTEs)
-- 27. Inheritance

-- --------------------------------------------------------------------------------
-- 1. Introduction
-- --------------------------------------------------------------------------------
-- Overview
-----------
-- PostgreSQL is a powerful open-source relational database management 
-- system (RDBMS). It uses and extends the SQL language combined with many 
-- features that safely store and scale the most complicated data workloads.

-- History
----------
-- PostgreSQL, often simply Postgres, was originally developed at the University
-- of California, Berkeley in 1986. It has more than 30 years of active development
-- and has earned a strong reputation for reliability, robustness, and performance.

-- Architecture
---------------
-- PostgreSQL uses a client/server model. A PostgreSQL session consists of the 
-- following processes:
-- 1. A server, which manages the database files, accepts connections from client
-- applications, and performs database actions on behalf of the clients.
-- 2. The user's client application that wants to perform database operations.

-- Components
-------------
-- Databases: A collection of schemas, which contains tables, functions, etc.
-- Schemas: A namespace that contains database objects like tables, views, etc.
-- Tables: Structures within a schema that hold data in rows and columns.
-- Rows: Individual records in a table.
-- Columns: Fields within a table that define the type of data stored.

-- SQL Commands
---------------
-- SQL commands in PostgreSQL are divided into several categories:
-- Data Definition Language (DDL): CREATE, ALTER, DROP, TRUNCATE
-- Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE
-- Data Control Language (DCL): GRANT, REVOKE
-- Transaction Control Language (TCL): BEGIN, COMMIT, ROLLBACK

-- --------------------------------------------------------------------------------
-- 2. Basic Syntax
-- --------------------------------------------------------------------------------

-- Comments
-----------
-- There are two types of comments: single-line and multi-line.

-- Single-line comment
/* Multi-line comment */

-- Semicolons
-------------
-- Each SQL statement should end with a semicolon.

SELECT 'Hello, PostgreSQL';

-- Case Sensitivity
-------------------
-- SQL Keywords are case-insensitive. Database, Table, and Column 
-- Names are also case-insensitive unless they are double-quoted.

CREATE DATABASE ExampleDB;
CREATE DATABASE "exampleDB"; -- This will create a different database

-- Schema Usage
---------------
-- Schemas are used to organize objects. The default schema is 'public'.

CREATE SCHEMA my_schema;
CREATE TABLE my_schema.my_table (id serial PRIMARY KEY);

-- Set the search path to prioritize 'my_schema'.
SET search_path TO my_schema, public;

-- --------------------------------------------------------------------------------
-- 3. Data Types
-- --------------------------------------------------------------------------------
-- PostgreSQL supports a wide variety of data types.

-- Numeric Types
INTEGER       -- Signed four-byte integer
BIGINT        -- Signed eight-byte integer
NUMERIC(p,s)  -- (precision,scale): Exact numeric of selectable precision
REAL          -- Single precision floating-point number
DOUBLE PRECISION -- Double precision floating-point number

-- Character Types
CHAR(n)       -- Fixed-length character string
VARCHAR(n)    -- Variable-length character string with limit
TEXT          -- Variable-length character string

-- Date/Time Types
DATE          -- Calendar date (year, month, day)
TIME          -- Time of day
TIMESTAMP     -- Date and time
INTERVAL      -- Time interval

-- Boolean Type
BOOLEAN       -- True or false

-- Enumerated Type
CREATE TYPE mood AS ENUM ('sad', 'happy');

-- Array Type
INTEGER[]     -- Array of integers
VARCHAR[]     -- Array of strings

-- JSON Types
JSON          -- Textual JSON data
JSONB         -- Binary JSON data, decomposed

-- Other Types
UUID          -- Universally Unique Identifiers
BYTEA         -- Binary data ("byte array")
CIDR          -- IPv4 or IPv6 network address
INET          -- IPv4 or IPv6 host address
MACADDR       -- MAC address

-- --------------------------------------------------------------------------------
-- 4. Database Operations
-- --------------------------------------------------------------------------------
-- Data is organized into databases, which contain schemas and tables.

-- Listing databases.
\l

-- Creating a database.
CREATE DATABASE my_database;

-- Dropping a database.
DROP DATABASE IF EXISTS my_database;

-- Connecting to a database.
\c my_database

-- --------------------------------------------------------------------------------
-- 5. Table Operations
-- --------------------------------------------------------------------------------
-- Tables are composed of columns (fields) and rows (records).

-- Creating a table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price NUMERIC(8, 2) NOT NULL,
    stock_quantity INTEGER,
    created_at DATE
);

-- Altering a table (add, modify, drop).
ALTER TABLE products ADD COLUMN description TEXT;
ALTER TABLE products ALTER COLUMN description SET NOT NULL;
ALTER TABLE products DROP COLUMN description;

-- Dropping a table.
DROP TABLE IF EXISTS products;

-- Describe table structure.
\d products

-- --------------------------------------------------------------------------------
-- 6. Constraints
-- --------------------------------------------------------------------------------
-- PostgreSQL constraints are used to specify rules for the data in a table.
-- They ensure the integrity of data in the database.
--
-- Common PostgreSQL constraints:
-- * PRIMARY KEY: Uniquely identifies each row in a table.
-- * FOREIGN KEY: Establishes a link between two tables.
-- * UNIQUE: Ensures all values in a column are distinct.
-- * NOT NULL: Ensures a column cannot have NULL values.
-- * CHECK: Ensures that all column values satisfy a specific condition.
-- * DEFAULT: Provides a default value, if no value is specified.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,                     -- PRIMARY KEY constraint
    product_id INTEGER UNIQUE,                       -- UNIQUE constraint
    customer_name VARCHAR(100) NOT NULL,             -- NOT NULL constraint
    order_date DATE NOT NULL,                        -- NOT NULL constraint
    quantity INTEGER CHECK (quantity > 0),           -- CHECK constraint
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP   -- DEFAULT constraint
    FOREIGN KEY (product_id) REFERENCES products(id) -- FOREIGN KEY constraint
);

-- Adding a constraint.
ALTER TABLE orders ADD CONSTRAINT chk_valid_order CHECK (order_date <= CURRENT_DATE);

-- Adding an exclusion constraint.
CREATE TABLE reservations (
    room_id INT,
    during TSRANGE,
    -- Enforce that no two rows can have the same (room_id and time range).
    EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

-- --------------------------------------------------------------------------------
-- 7. Data Manipulation
-- --------------------------------------------------------------------------------
-- Inserting/Updating data in your tables.

-- You can insert either a single row or multiple rows
INSERT INTO products (product_name, category, price, stock_quantity, created_at)
VALUES
    ('Laptop', 'Electronics', 999.99, 50, '2023-01-15'),
    ('Smartphone', 'Electronics', 799.99, 150, '2023-02-01'),
    ('Desk Chair', 'Furniture', 199.99, 75, '2023-03-10');

-- Updating the price of a specific product based on its name.
UPDATE products SET price = 879.99 WHERE product_name = 'Smartphone';

-- Deleting a specific product based on its stock_quantity.
DELETE FROM products WHERE stock_quantity = 0;

-- --------------------------------------------------------------------------------
-- 8. Querying Data
-- --------------------------------------------------------------------------------
-- Retrieving data from your tables.

-- Retrieve all columns for each product.
SELECT * FROM products;

-- Retrieve only specific columns for each product.
SELECT product_name, price, stock_quantity FROM products;

-- Retrieve all products with a price greater than 500.
SELECT * FROM products WHERE price > 500;

-- Results can be ordered: ascending (ASC) or descending (DESC).
SELECT * FROM products ORDER BY created_at DESC;

-- Retrieve the first 10 products with their names and prices.
SELECT product_name, price FROM products LIMIT 10;

-- Retrieve the next 10 products after the first 10 (offset).
SELECT product_name, price FROM products OFFSET 10 LIMIT 10;

-- Retrieve all unique product categories.
SELECT DISTINCT category FROM products;

-----------
-- 9. Joins
-----------
-- Joins are crucial for querying data spread across multiple tables.
-- They are used to combine rows from two or more tables based 
-- on a related column between them.
--
-- Main types of PostgreSQL joins:
-- * INNER JOIN: Returns rows with matching values in both tables.
-- * LEFT JOIN : Returns all rows from the left table, and matching 
--               rows from the right table. unmatched right rows are NULL.
-- * RIGHT JOIN: Returns all rows from the right table, and matching 
--               rows from the left table. unmatched left rows are NULL.
-- * FULL JOIN : Returns all rows with matches in either table
--               unmatched rows are NULL.
-- * CROSS JOIN: Returns the Cartesian product of two tables, showing all 
--               possible row combinations.
-- * SELF JOIN : Joins a table with itself to compare same table rows.

-- INNER JOIN: Retrieve the order details and product name for all orders.
SELECT orders.order_id, products.product_name, orders.customer_name, orders.quantity, orders.order_date
FROM orders
INNER JOIN products ON orders.product_id = products.id;

-- LEFT JOIN: Retrieve all products with their order, if available, else NULL.
SELECT products.product_name, orders.order_id, orders.customer_name, orders.quantity, orders.order_date
FROM products
LEFT JOIN orders ON products.id = orders.product_id;

-- RIGHT JOIN: Retrieve all orders with their product, if available, else NULL.
SELECT products.product_name, orders.order_id, orders.customer_name, orders.quantity, orders.order_date
FROM products
RIGHT JOIN orders ON products.id = orders.product_id;

-- FULL JOIN: Retrieve all matched or unmatched products and orders, else NULL.
SELECT products.product_name, orders.order_id
FROM products
FULL JOIN orders ON products.id = orders.product_id;

-- CROSS JOIN: Retrieve all possible combinations of products and categories.
SELECT products.product_name, categories.category_name
FROM products
CROSS JOIN categories;

-- --------------------------------------------------------------------------------
-- 10. Aggregate Functions
-- --------------------------------------------------------------------------------
-- Used to perform calculations on a set of values and return a single value.
-- Often used with the GROUP BY clause to group rows that have the same values.

-- Common Postgres aggregate functions:
-- * COUNT(): Counts the number of rows or non-null values.
-- * SUM(): Calculates the sum of a set of values.
-- * AVG(): Calculates the average of a set of values.
-- * MAX(): Returns the maximum value in a set.
-- * MIN(): Returns the minimum value in a set.
-- * ARRAY_AGG(): Collects values into an array.
-- * STRING_AGG(): Concatenates strings with a delimiter.

-- Get the total number of orders.
SELECT COUNT(*) AS total_orders FROM orders;

-- Get the total amount of all orders.
SELECT SUM(amount) AS total_amount FROM orders;

-- Get the product count and average price for each category.
SELECT category, COUNT(*) AS product_count, AVG(price) AS average_price
FROM products
GROUP BY category;

-- Get categories with more than 5 products.
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 5;

-- Get a list of products for each category as an array.
SELECT category, ARRAY_AGG(product_name) AS products
FROM products
GROUP BY category;

-- Get a comma-separated list of products for each category.
SELECT category, STRING_AGG(product_name, ', ') AS product_list
FROM products
GROUP BY category;

-- --------------------------------------------------------------------------------
-- 11. Subqueries
-- --------------------------------------------------------------------------------
-- A Subquery is a query nested inside another query. They are useful for 
-- breaking down complex queries and can be used in SELECT, WHERE, HAVING, 
-- FROM, and EXISTS clauses.
-- 
-- Types of Subqueries:
-- * Scalar: Returns a single value.
-- * Row: Returns a single row with multiple columns.
-- * Table: Returns multiple rows and columns.
-- * Correlated: References columns from the outer query.

-- Scalar: Retrieve products priced above the overall average.
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Row: Retrieve the product with the highest price across all categories.
SELECT *
FROM products
WHERE (category, price) = (
    SELECT category, MAX(price)
    FROM products
    GROUP BY category
    ORDER BY MAX(price) DESC
    LIMIT 1
);

-- Table: Retrieve products that have been ordered more than 100 times.
SELECT product_name, price
FROM products
WHERE id IN (
    SELECT product_id
    FROM orders
    GROUP BY product_id
    HAVING SUM(quantity) > 100
);

-- Correlated: Retrieve the most expensive product across all categories.
SELECT p.product_name, p.price
FROM products p
WHERE p.price > (
    SELECT AVG(price)
    FROM products
    WHERE category = p.category
);

-- --------------------------------------------------------------------------------
-- 12. Indexes
-- --------------------------------------------------------------------------------
-- Improve query performance by speeding up data retrieval. But may slow 
-- down data modification operations such as INSERT, UPDATE, and DELETE.
--
-- Types of indexes in PostgreSQL:
-- * B-tree: Default, for general use.
-- * Hash: Optimized for exact matches.
-- * GIST: Supports complex types, full-text search.
-- * SP-GiST: Efficient for non-balanced structures.
-- * GIN: Good for arrays and composite types.
-- * BRIN: Best for large, sequential data.

-- Create a B-tree index on product names.
CREATE INDEX idx_product_name ON products(product_name);

-- Create a unique B-tree index on SKU values.
CREATE UNIQUE INDEX idx_product_sku ON products(sku);

-- Create a B-tree index on category and price.
CREATE INDEX idx_product_category_price ON products(category, price);

-- Create a partial B-tree index on products with price greater than 1000.
CREATE INDEX idx_high_value_products ON products(price) WHERE price > 1000;

-- Create a B-tree index on lowercased product names.
CREATE INDEX idx_lower_product_name ON products(LOWER(product_name));

-- Drop the index on product names if it exists.
DROP INDEX IF EXISTS idx_product_name;

-- Rebuild all indexes for the products table.
REINDEX TABLE products;

-- --------------------------------------------------------------------------------
-- 13. Stored Procedures and Functions
-- --------------------------------------------------------------------------------
-- A set of SQL statements that can be stored and executed repeatedly.
-- They improve performance by reducing client-server communication.
--
-- * Procedures: Perform actions, may not return a value.
-- * Functions: Return a value, usable in SQL queries.

-- Create or replace a procedure to update a product's price.
CREATE OR REPLACE PROCEDURE update_product_price(
    product_id_param INTEGER,
    new_price NUMERIC
)
LANGUAGE plpgsql
-- Changes the delimiter to $$ to allow for multi-line statements.
AS $$
BEGIN
    UPDATE products
    SET price = new_price
    WHERE id = product_id_param;
END;
$$;

-- Call the procedure to update the price of product with ID 1.
CALL update_product_price(1, 1099.99);

-- Drop the procedure if it exists.
DROP PROCEDURE IF EXISTS update_product_price(INTEGER, NUMERIC);

-- Create or replace a function to get the count of products in a category.
CREATE OR REPLACE FUNCTION get_product_count(category_name VARCHAR)
RETURNS INTEGER AS $$
DECLARE
    product_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO product_count
    FROM products
    WHERE category = category_name;
    
    RETURN product_count;
END;
$$ LANGUAGE plpgsql;

-- Get the count of products in the 'Electronics' category.
SELECT get_product_count('Electronics');

-- Drop the function if it exists.
DROP FUNCTION IF EXISTS get_product_count(VARCHAR);

-- --------------------------------------------------------------------------------
-- 14. Transactions
-- --------------------------------------------------------------------------------
-- Transaction is a sequence of one or more SQL operations that are executed
-- as a single unit. If any statement fails, the entire transaction can be 
-- rolled back to maintain data integrity.
--
-- Transactions have the following properties (ACID):
-- * Atomicity: Entire transaction succeeds or fails as a whole.
-- * Consistency: Ensures valid state transitions in the database.
-- * Isolation: Transactions run independently of each other.
-- * Durability: Committed transactions persist, even after failures.

-- Start a transaction.
BEGIN;

-- Insert a new product.
INSERT INTO products (product_name, category, price, stock_quantity, created_at)
VALUES ('Laptop', 'Electronics', 999.99, 50, '2023-01-15');

-- if there's an error, roll back the transaction, discarding all changes.
ROLLBACK;

-- Commit the transaction, applying all changes.
COMMIT;


-- Starting another transaction.
BEGIN;

-- Insert a new product and set a savepoint.
INSERT INTO products (product_name, category, price, stock_quantity, created_at)
VALUES ('Smartphone', 'Electronics', 799.99, 150, '2023-02-01');

-- Set a savepoint.
SAVEPOINT my_savepoint;

-- Update stock quantity.
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE id = 1;

-- Roll back to the savepoint, undoing changes since the savepoint.
ROLLBACK TO my_savepoint;

-- Release the savepoint, removing it from the transaction.
RELEASE SAVEPOINT my_savepoint;

-- Commit the transaction, applying all changes up to the savepoint.
COMMIT;

-- --------------------------------------------------------------------------------
-- 15. Views
-- --------------------------------------------------------------------------------
-- Views are virtual tables based on the result of an SQL statement. 
-- They don't store data and provide a way to simplify complex queries, 
-- improve data security and ensure a consistent data interface.
--
-- Types of PostgreSQL views:
-- 1. Simple: Based on one table or basic joins.
-- 2. Complex: Involve multiple tables or complex queries.
-- 3. Updatable: Allow INSERT, UPDATE, and DELETE.
-- 4. Materialized: Store results physically for faster access.

-- Create a simple view of product names and prices.
CREATE VIEW product_overview AS
SELECT product_name, price
FROM products;

-- Query the product overview view.
SELECT * FROM product_overview;

-- Create a detailed view of orders with product details.
CREATE VIEW detailed_order_summary AS
SELECT 
    o.customer_name, p.product_name, p.price, o.quantity,
    (p.price * o.quantity) AS total_cost,
    o.order_date
FROM orders o
JOIN products p ON o.product_id = p.id;

-- Query the detailed order summary view.
SELECT * FROM detailed_order_summary;

-- Create a materialized view summarizing product sales.
CREATE MATERIALIZED VIEW product_sales_summary AS
SELECT 
    p.id AS product_id,
    p.product_name,
    SUM(o.quantity) AS total_quantity_sold,
    SUM(p.price * o.quantity) AS total_revenue
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY p.id, p.product_name;

-- Refresh the materialized view to update data.
REFRESH MATERIALIZED VIEW product_sales_summary;

-- Query the refreshed materialized view.
SELECT * FROM product_sales_summary;

-- Drop views and materialized view if they exist.
DROP VIEW IF EXISTS product_overview;
DROP VIEW IF EXISTS detailed_order_summary;
DROP MATERIALIZED VIEW IF EXISTS product_sales_summary;

-- --------------------------------------------------------------------------------
-- 16. Triggers
-- --------------------------------------------------------------------------------
-- Triggers are database objects that are automatically executed when 
-- certain events occurs. 
-- 
-- Types of triggers in MySQL:
-- * BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, 
--   BEFORE DELETE and AFTER DELETE.
--
-- NEW: New row of data that is being inserted or updated.
-- OLD: Existing row of data before it is updated or deleted.

-- Define a function to update the stock quantity.
CREATE OR REPLACE FUNCTION update_stock_quantity()
RETURNS TRIGGER AS $$
BEGIN
    -- Decrease stock quantity based on the new order.
    UPDATE products 
    SET stock_quantity = stock_quantity - NEW.quantity
    WHERE id = NEW.product_id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create a trigger to call the function after an order is inserted.
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION update_stock_quantity();

-- Define a function to check the stock before inserting an order.
CREATE OR REPLACE FUNCTION check_stock_before_insert()
RETURNS TRIGGER AS $$
DECLARE
    stock INT;
BEGIN
    -- Check current stock quantity for the product.
    SELECT stock_quantity INTO stock FROM products WHERE id = NEW.product_id;
    
    -- Raise an exception if stock is insufficient.
    IF stock < NEW.quantity THEN
        RAISE EXCEPTION 'Insufficient stock for product ID %', NEW.product_id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create a trigger to call the function before an order is inserted.
CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION check_stock_before_insert();

-- Drop the trigger if it exists.
DROP TRIGGER IF EXISTS before_order_insert ON orders;

-- Drop the stock checking function if it exists.
DROP FUNCTION IF EXISTS check_stock_before_insert();

-- --------------------------------------------------------------------------------
-- 17. User Management
-- --------------------------------------------------------------------------------
-- User management involves creating, modifying, and deleting user 
-- accounts, as well as granting and revoking privileges.

-- Create a new user with a password.
CREATE USER new_user WITH PASSWORD 'password123';

-- Create a superuser with a password.
CREATE USER admin_user WITH SUPERUSER PASSWORD 'adminpassword';

-- Grant SELECT and INSERT privileges on the products table to new_user.
GRANT SELECT, INSERT ON products TO new_user;

-- Grant all privileges on the public schema to new_user.
GRANT ALL PRIVILEGES ON SCHEMA public TO new_user;

-- Create a read-only role.
CREATE ROLE read_only;

-- Grant SELECT on all tables in the public schema to the read_only role.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- Grant the read_only role to new_user.
GRANT read_only TO new_user;

-- Revoke INSERT privilege on the products table from new_user.
REVOKE INSERT ON products FROM new_user;

-- Drop the user new_user if it exists.
DROP USER IF EXISTS new_user;

-- Drop the read_only role.
DROP ROLE read_only;

-- --------------------------------------------------------------------------------
-- 18. Backup and Restore
-- --------------------------------------------------------------------------------
-- Regular backups are crucial for data safety and recovery.

-- Backup the entire database to a SQL file.
pg_dump -U username -d database_name -f backup_file.sql;

-- Backup specific tables to a SQL file.
pg_dump -U username -d database_name -t table1 -t table2 -f backup_file.sql;

-- Backup only the schema (no data) to a SQL file.
pg_dump -U username -d database_name --schema-only -f schema_backup.sql;

-- Backup the database to a custom-format backup file.
pg_dump -U username -d database_name -F c -f backup_file.custom;

-- Backup the database to a directory format.
pg_dump -U username -d database_name -F d -f backup_directory;

-- Restore database from a custom-format backup file.
pg_restore -U username -d database_name backup_file.custom;

-- Restore database from a directory-format backup.
pg_restore -U username -d database_name backup_directory;

-- Restore database from a SQL file.
psql -U username -d database_name -f backup_file.sql;

-- Physical Backup
------------------
-- Creates binary copy of the database cluster to a specified directory.
pg_basebackup -U username -D backup_directory -Fp -Xs -P;

-- To Restore: 
-- 1. Stop the PostgreSQL server.
-- 2. Replace the existing data directory with the backup directory.
-- 3. Start the PostgreSQL server.

-- Point-in-Time Recovery
-------------------------
-- 1. Enable WAL Archiving:
ALTER SYSTEM SET archive_mode = 'on';
ALTER SYSTEM SET archive_command = 'cp %p /your/archive/directory/%f';
SELECT pg_reload_conf();

-- 2. Take Base Backup:
pg_basebackup -D /path/to/backup -Ft -z -P --wal-method=stream

-- 3. Stop the PostgreSQL server:
pg_ctl stop -D /path/to/data_directory

-- 4. Restore Backup:
tar -xvf /path/to/backup/base.tar.gz -C /path/to/data_directory

-- 5. Create recovery.conf with:
restore_command = 'cp /your/archive/directory/%f %p'
recovery_target_time = 'YYYY-MM-DD HH:MI:SS'

-- 6. Start the PostgreSQL server:
pg_ctl start -D /path/to/data_directory

-- --------------------------------------------------------------------------------
-- 19. Concurrency Control
-- --------------------------------------------------------------------------------
-- Concurrency Control ensures that multiple transactions can be executed
-- simultaneously, without interfering with each other, maintaining integrity
-- and consistency. By Default Postgres uses Multi-Version Concurrency Control.
--
-- Key concepts Concurrency Control:
-- * Multi-Version Concurrency Control (MVCC): Allows multiple versions of a 
--   row to exist, helping to manage concurrent access without locking.
-- * Isolation Levels: Controls visibility of transaction changes.
-- * Locks: Prevent conflicts between transactions.
-- * Deadlocks: Occur when transactions wait on each other's locks.

-- Isolation Levels
-------------------
-- * READ COMMITTED: Default. Sees data committed before each query.
-- * READ UNCOMMITTED: Can read uncommitted changes.
-- * REPEATABLE READ: Sees the same snapshot for all queries in the transaction.
-- * SERIALIZABLE: Ensures transactions act as if executed sequentially.

-- Setting an isolation level
BEGIN;
-- Set isolation level to Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Perform some operations
SELECT * FROM products WHERE category = 'Electronics';
-- Commit the transaction
COMMIT;

-- Locks
--------
-- PostgreSQL lock types:
-- * Row-Level Locks:
--   - FOR UPDATE: Blocks row modifications and locks by others.
--   - FOR SHARE: Blocks modifications, allows reading.
-- * Table-Level Locks:
--   - ACCESS SHARE: Allows reads, blocks exclusive locks.
--   - ROW SHARE: Allows reads, row-level updates.
--   - ROW EXCLUSIVE: Allows inserts, updates, deletes.
--   - SHARE UPDATE EXCLUSIVE: Blocks schema changes, VACUUM.
--   - SHARE: Blocks modifications, allows reads.
--   - SHARE ROW EXCLUSIVE: Blocks modifications and certain locks.
--   - EXCLUSIVE: Allows only ACCESS SHARE locks.
--   - ACCESS EXCLUSIVE: Blocks all access.
-- * Advisory Locks: User-defined, for custom use cases.

-- Row-level locking:
BEGIN;
-- Locking row to update its stock quantity.
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- Update the locked row.
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE id = 1;
-- Commit the transaction.
COMMIT;

-- Table-level locking:
BEGIN;
-- Lock the 'products' table in EXCLUSIVE mode
LOCK TABLE products IN EXCLUSIVE MODE;
-- Perform some operations
-- Commit the transaction
COMMIT;

-- Deadlock:
-- Transaction 1:
BEGIN;
-- Lock row 1 for update.
UPDATE example SET value = value + 1 WHERE id = 1;
-- Attempt to lock row 2, which is already locked by Transaction 2.
UPDATE example SET value = value + 1 WHERE id = 2;
-- Commit (will not reach due to deadlock).
COMMIT;

-- Transaction 2:
BEGIN;
-- Lock row 2 for update.
UPDATE example SET value = value + 1 WHERE id = 2;
-- Attempt to lock row 1, which is already locked by Transaction 1.
UPDATE example SET value = value + 1 WHERE id = 1;
-- Commit (will not reach due to deadlock).
COMMIT;

-- PostgreSQL detects the deadlock and aborts one of the transactions.

-- --------------------------------------------------------------------------------
-- 20. Replication
-- --------------------------------------------------------------------------------
-- Is a process that allows you to automatically copy data from one 
-- database server (the master) to one or more database servers (the slaves).
--
-- Benefits :
-- High availability by allowing a slave to be promoted to master in case of 
-- failure, enables load balancing by distributing read queries across slaves, 
-- facilitates backups on a slave, and allows intensive data analysis.
--
-- Key concepts:
-- * Primary: Source database server with original data.
-- * Standby: Destination server receiving data copies.
-- * WAL: Log of all changes, like MySQL's binary log.
-- * Streaming Replication: Real-time WAL transfer to standby.
-- * Physical Replication: Byte-by-byte copy of the database.
-- * Logical Replication: Replication based on object-level changes.

-- PostgreSQL Primary Configuration:
-- Set up WAL for replication.
wal_level = replica    -- WAL level for replication
max_wal_senders = 10   -- Max concurrent WAL sender processes
wal_keep_size = '1GB'  -- Size of WAL to keep for replication
hot_standby = on       -- Allow queries on standby

-- Create replication user.
CREATE USER repl_user WITH REPLICATION ENCRYPTED PASSWORD 'password';

-- Allow replication connections from replica.
host replication repl_user <replica_ip_address>/24 md5

-- Perform a base backup from the primary server.
pg_basebackup -h <primary_ip_address> -D /path_to_data -U replicator -P --wal-method=stream

-- PostgreSQL Replica Configuration:
-- Enable hot standby and configure primary connection.
hot_standby = on
primary_conninfo = 'host=<primary_ip> port=5432 user=replicator password=replica_password'
trigger_file = '/tmp/postgresql.trigger'

-- Start the standby server.
pg_ctl start -D /path/to/standby/data

-- Monitor replication status.
SELECT * FROM pg_stat_replication;

-- Check the WAL receiver status.
SELECT * FROM pg_stat_wal_receiver;

-- Pause WAL replay on the replica.
SELECT pg_wal_replay_pause();

-- Resume WAL replay on the replica.
SELECT pg_wal_replay_resume();

-- Promote the replica to primary.
pg_ctl promote -D /path/to/standby/data

-- --------------------------------------------------------------------------------
-- 21. Partitioning
-- --------------------------------------------------------------------------------
-- Is a database feature that allows you to divide a large table into 
-- smaller, more manageable pieces, yet still treat them as a single table.
--
-- Main types of partitioning in PostgreSQL:
-- 1. Range: Based on a range of values (e.g., date ranges).
-- 2. List : Based on a list of known values (e.g., categories).
-- 3. Hash : Based on a hash of the partition key.

-- Create a range-partitioned table by creation date.
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(8, 2) NOT NULL,
    stock_quantity INT,
    created_at DATE
) PARTITION BY RANGE (created_at);

-- Create a partition for the year 2023.
CREATE TABLE products_2023 PARTITION OF products
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- Create a partition for the year 2024.
CREATE TABLE products_2024 PARTITION OF products
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Create a default partition for any non-matching dates.
CREATE TABLE products_default PARTITION OF products DEFAULT;

-- Create a list-partitioned table by category.
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(8, 2) NOT NULL,
    stock_quantity INT,
    created_at DATE
) PARTITION BY LIST (category);

-- Create a partition for Electronics.
CREATE TABLE products_electronics PARTITION OF products
FOR VALUES IN ('Electronics');

-- Create a partition for Furniture.
CREATE TABLE products_furniture PARTITION OF products
FOR VALUES IN ('Furniture');

-- Create a default partition for any non-matching categories.
CREATE TABLE products_default PARTITION OF products DEFAULT;

-- After partitioning a table, you can insert, update, delete, and 
-- query data without any special modification to your queries.

-- Detach a partition, making it a standalone table.
ALTER TABLE products DETACH PARTITION products_2023;

-- Reattach a detached partition or add a new table as a partition.
ALTER TABLE products ATTACH PARTITION products_2023 
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- Permanently delete a partition and its data.
DROP TABLE products_2023;

-- --------------------------------------------------------------------------------
-- 22. Extensions
-- --------------------------------------------------------------------------------
-- Extensions are packages that add functionality to the database.
-- They can include additional data types, functions, operators, and more.
--
-- Key aspects of PostgreSQL Extensions:
-- 1. Modularity: Encapsulate related objects and functionality.
-- 2. Versioning: Extensions can be upgraded or downgraded.
-- 3. Dependency management: Extensions can depend on other extensions.
-- 4. Easy installation: Can be installed with a single SQL command.
--
-- Common PostgreSQL Extensions:
-- * PostGIS: Adds support for geographic objects.
-- * pgcrypto: Provides cryptographic functions.
-- * hstore: Implements key-value pair storage.
-- * uuid-ossp: Generates universally unique identifiers (UUIDs).

-- List available extensions.
SELECT * FROM pg_available_extensions;

-- List installed extensions.
SELECT * FROM pg_extension;

-- Install the hstore extension if it's not already installed.
CREATE EXTENSION IF NOT EXISTS hstore;

-- Create a table with an hstore column.
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes hstore  -- hstore extension column
);

-- Insert data into the table with hstore attributes.
INSERT INTO products (name, attributes) VALUES
('Laptop', 'brand => "Dell", cpu => "Intel i7", ram => "16GB"');

-- Update the hstore extension to the latest version.
ALTER EXTENSION hstore UPDATE;

-- Remove the hstore extension if it exists.
DROP EXTENSION IF EXISTS hstore;
-- --------------------------------------------------------------------------------
-- 23. JSON and JSONB
-- --------------------------------------------------------------------------------
-- PostgreSQL provides robust support for JSON, offering two main options:
-- 1. JSON: Stores data as plain text.
-- 2. JSONB: Stores data in a binary format for faster processing and indexing.

-- Create tables with JSON and JSONB columns.
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_details JSON
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_data JSONB
);

-- Inserting JSON data.
INSERT INTO orders (order_details) VALUES
('{"items": [{"product_id": 1, "quantity": 2}, {"product_id": 2, "quantity": 1}], "total": 1229.98, "status": "pending"}');

-- Insert JSONB data.
INSERT INTO products (product_data) VALUES
('{"name": "Laptop", "brand": "LaptopBrand", "specs": {"cpu": "i7", "ram": "16GB", "storage": "512GB SSD"}, "price": 999.99}'),
('{"name": "Mouse", "brand": "MouseBrand", "specs": {"dpi": "16000", "buttons": 8}, "price": 59.99}');

-- Retrieve the list of items from a specific order, '->' to extract JSON.
SELECT order_details->'items' AS items FROM orders;

-- Extract the product name as text from product_data, '->>' to extract text.
SELECT product_data->>'name' AS product_name FROM products;

-- Extract the CPU specification from the "specs" object (returns text).
SELECT product_data->'specs'->>'cpu' AS cpu FROM products WHERE product_data->>'name' = 'Laptop';

-- Retrieve the product_id of the first item in the order's items array.
SELECT order_details->'items'->0->>'product_id' AS first_item_id FROM orders;

-- Update the price of a product in the JSONB column.
UPDATE products SET product_data = jsonb_set(product_data, '{price}', '1099.99')
WHERE product_data->>'name' = 'Laptop';

-- Add a new field to the JSON data in the orders table.
UPDATE orders SET order_details = order_details || '{"shipping_method": "express"}'::json
WHERE (order_details->>'total')::numeric > 1000;

-- Remove a field from the JSONB data in the products table.
UPDATE products SET product_data = product_data - 'brand'
WHERE product_data->>'name' = 'Mouse';

-- json_each: Expands the outermost JSON object into key-value pairs.
SELECT * FROM json_each((SELECT order_details FROM orders LIMIT 1));

-- jsonb_object_keys: Returns the top-level keys of a JSONB object.
SELECT jsonb_object_keys(product_data) FROM products LIMIT 1;

-- jsonb_array_elements: Expands a JSON array to a set of JSON values.
SELECT jsonb_array_elements(order_details->'items') FROM orders;

-- jsonb_build_object: Builds a JSONB object from key-value pairs
INSERT INTO products (product_data) 
SELECT jsonb_build_object('name', 'Keyboard', 'brand', 'KeyboardBrand', 'price', 79.99);

-- jsonb_pretty: Formats JSONB data with indentation.
SELECT jsonb_pretty(product_data) FROM products LIMIT 1;

-- Check if JSONB contains (@>) specified key-value pair.
SELECT * FROM products WHERE product_data @> '{"brand": "LaptopBrand"}';

-- Check if JSONB is contained by (<@) another JSONB.
SELECT '{"name": "Laptop", "brand": "LaptopBrand"}'::jsonb <@ product_data FROM products;

-- Check if JSON object contains (?) the specified key.
SELECT * FROM orders WHERE order_details ? 'shipping_method';

-- Check if JSON object contains any (?|) of the specified keys.
SELECT * FROM products WHERE product_data ?| array['name', 'color'];

-- Check if JSON object contains all (?&) of the specified keys.
SELECT * FROM products WHERE product_data ?& array['name', 'price'];

-- --------------------------------------------------------------------------------
-- 24. Full-Text Search
-- --------------------------------------------------------------------------------
-- A powerful feature for fast and flexible text searching and 
-- ranking (how well a document matches a query) in large text fields.
--
-- Main components:
-- * tsvector: Text search vector.
-- * tsquery: Text search query.
-- * to_tsvector(): Converts text to tsvector.
-- * to_tsquery(): Converts search string to tsquery.
-- * @@: Text search match operator.

-- Creating a table with a text column.
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT
);

-- Inserting sample data.
INSERT INTO articles (title, content) VALUES
    ('PostgreSQL Basics', 'PostgreSQL is a powerful, open-source relational database system.'),
    ('Advanced SQL', 'SQL is a standard language for storing, manipulating, and retrieving data in databases.'),
    ('Full-Text Search', 'Full-text search allows efficient searching of large volumes of text data.');

-- Basic full-text search using to_tsvector and to_tsquery.
SELECT title FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database');

-- Adding a dedicated tsvector column for better performance.
ALTER TABLE articles ADD COLUMN content_tsv TSVECTOR;
UPDATE articles SET content_tsv = to_tsvector('english', content);

-- Creating an index on the tsvector column.
CREATE INDEX content_tsv_idx ON articles USING GIN (content_tsv);

-- Searching with multiple terms using AND operator.
SELECT title FROM articles
WHERE content_tsv @@ to_tsquery('english', 'database & search');

-- Ranking search results by relevance.
SELECT title, ts_rank(content_tsv, query) AS rank
FROM articles, to_tsquery('english', 'database | search') query
WHERE content_tsv @@ query
ORDER BY rank DESC;

-- Highlighting matched text in search results.
SELECT ts_headline('english', content, to_tsquery('english', 'database'),
       'StartSel = <b>, StopSel = </b>, MaxWords=50, MinWords=5')
FROM articles
WHERE content_tsv @@ to_tsquery('english', 'database');

-- Searching for an exact phrase in the content.
SELECT title FROM articles
WHERE content_tsv @@ phraseto_tsquery('english', 'open-source database');

-- Automatically updating tsvector column on insert or update.
CREATE FUNCTION articles_trigger() RETURNS trigger AS $$
BEGIN
  NEW.content_tsv := to_tsvector('english', NEW.content);
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

-- Trigger to update the tsvector column whenever content changes.
CREATE TRIGGER tsvector_update BEFORE INSERT OR UPDATE
ON articles FOR EACH ROW EXECUTE FUNCTION articles_trigger();

-- Creating a table for articles stored as JSONB data.
CREATE TABLE json_articles (
    id SERIAL PRIMARY KEY,
    data JSONB
);

-- Inserting a sample article as JSON data.
INSERT INTO json_articles (data) VALUES
    ('{"title": "JSON in PostgreSQL", "content": "PostgreSQL offers robust support for JSON data types."}');

-- Full-text search within JSON content.
SELECT data->>'title'
FROM json_articles
WHERE to_tsvector('english', data->>'content') @@ to_tsquery('english', 'json');

-- --------------------------------------------------------------------------------
-- 25. Window Functions
-- --------------------------------------------------------------------------------
-- Perform calculations across a set of rows related to the current row, 
-- without grouping them, allowing each row to retain its identity.

-- Syntax:
-- function_name ([exp]) OVER ([PARTITION BY exp] [ORDER BY exp])
--
-- Commonly used window functions:
-- 1. ROW_NUMBER(): Assigns a unique sequential integer per partition.
-- 2. RANK(): Rank each row within a partition, with gaps in rank for ties.
-- 3. DENSE_RANK(): Similar to RANK(), but without gaps in rank.
-- 4. NTILE(): Distributes rows into a specified number of buckets.
-- 5. LAG()/LEAD(): Accesses previous/next row within the same result set.
-- 6. SUM(), AVG(), COUNT(), etc.: Can also be used as window functions.

-- Assign a unique row number to each order per product, ordered by date.
SELECT product_id, customer_name, order_date,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY order_date) AS row_num
FROM orders;

-- Rank orders per product based on the quantity, highest quantity = rank 1.
SELECT product_id, customer_name, quantity,
    RANK() OVER (PARTITION BY product_id ORDER BY quantity DESC) AS rank
FROM orders;

-- Retrieve the quantity of the next order for each order per product.
SELECT product_id, customer_name, quantity,
    LEAD(quantity, 1) OVER (PARTITION BY product_id ORDER BY order_date) AS next_quantity
FROM orders;

-- Calculate total of quantity ordered for each product, ordered by date.
SELECT product_id, customer_name, quantity,
    SUM(quantity) OVER (PARTITION BY product_id ORDER BY order_date) AS running_total
FROM orders;

-- --------------------------------------------------------------------------------
-- 26. Common Table Expressions (CTEs)
-- --------------------------------------------------------------------------------
-- CTEs are temporary result sets that can be referenced within a 
-- `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statement in PostgreSQL.
--
-- Benefits:
-- * Improve query readability by breaking down complex queries.
-- * Reusable within the query and aid in recursive query scenarios.
--
-- Main Types of CTEs:
-- 1. Non-Recursive CTEs: Simple CTEs that don’t refer back to themselves.
-- 2. Recursive CTEs: Reference themselves, useful for hierarchical data.

-- Select products with a price greater than 100.
WITH TopProducts AS (
  SELECT product_id, name, price
  FROM Products
  WHERE price > 100
)
SELECT * FROM TopProducts;

-- Update prices for 'Electronics' and select updated product IDs.
WITH UpdatedProducts AS (
    UPDATE Products
    SET price = price * 1.1
    WHERE category = 'Electronics'
    RETURNING product_id
)
SELECT product_id
FROM UpdatedProducts;

-- Apply a 10% discount to 'Electronics' category products.
WITH DiscountedProducts AS (
  SELECT product_id, price
  FROM Products
  WHERE category = 'Electronics'
)
UPDATE Products SET price = price * 0.9
WHERE product_id IN (SELECT product_id FROM DiscountedProducts);

-- Recursive CTE to get the hierarchy of the 'Electronics' category.
WITH RECURSIVE CategoryHierarchy AS (
  -- Select the base category 'Electronics'.
  SELECT id, name, parent_id
  FROM Categories
  WHERE name = 'Electronics'

  UNION ALL

  -- Recursively select child categories.
  SELECT c.id, c.name, c.parent_id
  FROM Categories c
  INNER JOIN CategoryHierarchy ch ON c.parent_id = ch.id
)
SELECT * FROM CategoryHierarchy;

-- --------------------------------------------------------------------------------
-- 27. Inheritance
-- --------------------------------------------------------------------------------
-- A powerful feature that allows a table to inherit columns and 
-- constraints from a parent table.
-- 
-- Key concepts:
-- * Parent table: Base table with common columns.
-- * Child table: Inherits from parent, adds specific columns.
-- * Inheritance hierarchy: Supports multiple inheritance levels.
-- * Data distribution: Each table stores its own data.
-- * Polymorphic queries: Query parent to retrieve data from children.

-- Create a parent table for products.
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price NUMERIC(8, 2) NOT NULL,
    stock_quantity INTEGER,
    created_at DATE
);

-- Create a child table for electronics, inheriting from products.
CREATE TABLE electronics (
    warranty_period INTEGER
) INHERITS (products);

-- Insert into the parent table.
INSERT INTO products (product_name, category, price, stock_quantity, created_at) 
VALUES ('Generic Product', 'Miscellaneous', 19.99, 100, CURRENT_DATE);

-- Insert into the child with inherited columns and additional data.
INSERT INTO electronics (product_name, category, price, stock_quantity, created_at, warranty_period) 
VALUES ('Smartphone', 'Electronics', 699.99, 50, CURRENT_DATE, 24);

-- Select all rows, including inherited rows, from the parent.
SELECT * FROM products;

-- Select only rows from the parent, excluding inherited rows.
SELECT * FROM ONLY products;

-- Select all rows from the child table.
SELECT * FROM electronics;

-- Add a column to the parent, which also adds it to the child.
ALTER TABLE products ADD COLUMN supplier VARCHAR(100);

-- Drop the child table.
DROP TABLE electronics;

-- Dropping the parent won't drop the child tables automatically.
DROP TABLE products;