↑

Creating Tables in PostgreSQL

  1. Introduction



  2. Basic Syntax
    3. CREATE TABLE table_name (
    column_name1 data_type [constraint],
    column_name2 data_type [constraint],
    ...
    [table_constraints]
);



  3. Example: Creating a Simple Table
    4. CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    hire_date   DATE NOT NULL DEFAULT CURRENT_DATE,
    active      BOOLEAN DEFAULT TRUE
);



  4. Modern Auto-Increment Syntax (Preferred)
    5. CREATE TABLE employees (
    employee_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name  TEXT NOT NULL,
    last_name   TEXT NOT NULL,
    department  TEXT DEFAULT 'General'
);



  5. Adding Table-Level Constraints
    6. CREATE TABLE orders (
    order_id      INT GENERATED ALWAYS AS IDENTITY,
    customer_id   INT NOT NULL,
    order_total   NUMERIC(10,2) NOT NULL CHECK (order_total >= 0),
    order_date    TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);



  6. Common Data Types
    7. Type Example Description
    INTEGER, BIGINT, SMALLINT 42 Whole numbers, various ranges.
    NUMERIC(p, s) 1234.56 Fixed-point precision numbers (money, totals).
    TEXT, VARCHAR(n), CHAR(n) 'Hello' Variable or fixed-length strings.
    DATE, TIME, TIMESTAMP '2025-01-01' Date and time storage.
    BOOLEAN TRUE / FALSE Logical values.
    JSON, JSONB '{"key": "value"}' Structured JSON data (JSONB is binary-optimized).


  7. Creating Tables in a Specific Schema
    8. CREATE SCHEMA company;

CREATE TABLE company.departments (
    id   INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);



  8. Viewing Tables
    9. -- List all tables in current database
\dt

-- List tables in a specific schema
\dt company.*

-- Show table structure
\d employees



  9. Altering or Dropping Tables
    10. ALTER TABLE employees ADD COLUMN email TEXT;
ALTER TABLE employees DROP COLUMN active;
DROP TABLE employees;



Understanding Schemas in PostgreSQL

  1. What is a Schema?



  2. Database vs Schema

    3. 
Database → Schema → Table → Row


    
company_db
 ├── public
 │    ├── employees
 │    ├── departments
 │
 └── analytics
      ├── sales_summary
      ├── monthly_reports



  3. The Default Schema: public

    4. -- This table is created inside the default "public" schema
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);



  4. Creating a Custom Schema
    5. CREATE SCHEMA hr;
CREATE SCHEMA sales;


    CREATE TABLE hr.employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    position TEXT
);

CREATE TABLE sales.orders (
    id SERIAL PRIMARY KEY,
    amount NUMERIC(10,2),
    customer TEXT
);



  5. Accessing Tables in Schemas
    6. -- Access table using schema-qualified name
SELECT * FROM hr.employees;

-- Access table from the default "public" schema
SELECT * FROM public.users;



  6. The Search Path

    7. SHOW search_path;

-- Output example:
-- "public", "$user"


    SET search_path TO hr, public;

-- Now "employees" means "hr.employees"
SELECT * FROM employees;



  7. Dropping a Schema
    8. DROP SCHEMA hr;

-- Or remove all its objects automatically:
DROP SCHEMA hr CASCADE;



  8. Schema Permissions

    9. -- Grant a user permission to create objects in a schema
GRANT CREATE ON SCHEMA sales TO alice;

-- Allow a user to use (read from) the schema
GRANT USAGE ON SCHEMA hr TO bob;



  9. When to Use Schemas



  10. Summary


Inserting Data into Tables

  1. Introduction



  2. Basic Syntax
    3. INSERT INTO <table_name> (<column1>, <column2>, <column3>, ...)
VALUES (<value1>, <value2>, <value3>, ...);


  3. Example: Simple Insert
    4. INSERT INTO employees (first_name, last_name, department)
VALUES ('Alice', 'Johnson', 'Engineering');



  4. Inserting into All Columns
    5. INSERT INTO employees
VALUES (DEFAULT, 'Bob', 'Smith', 'Sales');



  5. Inserting Multiple Rows at Once
    6. INSERT INTO employees (first_name, last_name, department)
VALUES
    ('Charlie', 'Wang', 'Finance'),
    ('Diana', 'Lopez', 'Marketing'),
    ('Ethan', 'Neuer', 'IT');



  6. Using DEFAULT Values
    7. INSERT INTO employees (first_name, last_name)
VALUES ('Frank', 'Baker');

INSERT INTO employees (first_name, last_name, department)
VALUES ('Grace', 'Kim', DEFAULT);



  7. Inserting Data from Another Table
    8. INSERT INTO archived_employees (first_name, last_name, department)
SELECT first_name, last_name, department
FROM employees
WHERE active = FALSE;



  8. Returning Values After Insert
    9. INSERT INTO employees (first_name, last_name, department)
VALUES ('Helene', 'Zabak', 'Operations')
RETURNING employee_id;



  9. Handling Conflicts (Upserts)
    10. INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (1, 'Ian', 'Miller', 'Engineering')
ON CONFLICT (employee_id) DO UPDATE SET department = EXCLUDED.department;



  10. Example: Full Insert Scenario
    11. CREATE TABLE customers (
    id           SERIAL PRIMARY KEY,
    name         TEXT NOT NULL,
    email        TEXT UNIQUE,
    created_at   TIMESTAMP DEFAULT NOW()
);

INSERT INTO customers (name, email)
VALUES
    ('Alice Green', 'alice@example.com'),
    ('Bob White', 'bob@example.com');

INSERT INTO customers (name, email)
VALUES ('Alice Green', 'alice@example.com')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;



Basics on Selecting Data from Tables

  1. Introduction



  2. Basic Syntax
    3. SELECT <column1>, <column2>, ...
FROM <table_name>;



  3. Example: Select All Columns
    4. SELECT * FROM employees;



  4. Filtering Rows with WHERE
    5. SELECT first_name, last_name
FROM employees
WHERE department = 'Engineering';



  5. Filtering with Pattern Matching (LIKE)
    6. SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'S%';



  6. Sorting Results with ORDER BY
    7. SELECT first_name, last_name, hire_date
FROM employees
ORDER BY hire_date DESC;



  7. Limiting Results with LIMIT and OFFSET
    8. SELECT * FROM employees
ORDER BY employee_id
LIMIT 5 OFFSET 10;



  8. Aliasing Columns and Tables
    9. SELECT
    first_name AS given,
    last_name AS family
FROM employees AS e;



  9. Combining Conditions
    10. SELECT first_name, last_name, department
FROM employees
WHERE department = 'Sales'
    AND hire_date >= '2024-01-01'
    OR active = TRUE;



  10. Distinct Rows
    11. SELECT DISTINCT department
FROM employees;



  11. Computed Columns and Expressions
    12. SELECT
    first_name || ' ' || last_name AS full_name,
    EXTRACT(YEAR FROM hire_date)   AS hire_year
FROM employees;



  12. Aggregating Data with GROUP BY
    13. SELECT
    department,
    COUNT(*) AS total_employees
FROM employees
GROUP BY department;



  13. Filtering Groups with HAVING
    14. SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;



  14. Combining Results with UNION
    15. SELECT first_name, last_name FROM employees
UNION
SELECT first_name, last_name FROM contractors;



Concatenating Strings in PostgreSQL

  1. Introduction



  2. Using the || Operator
    3. SELECT 'Hello' || ' ' || 'World' AS greeting;



  3. Handling NULLs with ||
    4. SELECT 'Hello' || NULL || 'World' AS result;



  4. Using CONCAT()
    5. SELECT CONCAT('Hello', ' ', 'World') AS greeting;



  5. Using CONCAT_WS() — Concatenate with Separator
    6. SELECT CONCAT_WS('-', '2025', '11', '10') AS formatted_date;



  6. Concatenating Columns Example
    7. SELECT
    first_name || ' ' || last_name AS full_name,
    department || ' Department'    AS dept_name
FROM employees;


  7. Concatenation in UPDATE Statements
    8. UPDATE employees
SET full_name = first_name || ' ' || last_name;



  8. Concatenation with Numbers and Other Types
    9. SELECT
    'Employee ID: ' || employee_id AS label
FROM employees;



  9. Formatting Example: Full Address
    10. SELECT
    CONCAT_WS(', ',
        street,
        city,
        postal_code,
        country
    ) AS full_address
FROM customers;



Dropping Tables in PostgreSQL

  1. Introduction



  2. Basic Syntax
    3. DROP TABLE <table_name>;



  3. Example: Dropping a Table
    4. DROP TABLE employees;



  4. Safely Dropping a Table
    5. DROP TABLE IF EXISTS employees;



  5. Dropping Multiple Tables
    6. DROP TABLE IF EXISTS employees, departments, projects;



  6. Using CASCADE and RESTRICT
    7. DROP TABLE employees CASCADE;



  7. Example: Dropping with Dependencies
    8. -- Example: "customers" table references "orders" through a foreign key
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY
);

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id)
);

-- Dropping the parent table with CASCADE
DROP TABLE orders CASCADE;



  8. Dropping a Table in a Specific Schema
    9. DROP TABLE company.employees;



  9. Checking if a Table Exists
    10. -- Using psql meta-command
\dt

-- Or query the catalog directly
SELECT tablename
FROM pg_catalog.pg_tables
WHERE tablename = 'employees';



  10. Practical Tip: Use Transactions
    11. BEGIN;
DROP TABLE employees;
ROLLBACK;



Filtering Rows with the WHERE Clause

  1. Introduction



  2. Basic Syntax
    3. SELECT column1, column2, ...
FROM table_name
WHERE condition;



  3. Comparison Operators
    4. SELECT * FROM employees
WHERE department = 'Engineering';



  4. Combining Conditions with AND, OR, NOT
    5. SELECT * FROM employees
WHERE department = 'Sales'
    AND hire_date >= '2024-01-01'
    OR active = TRUE;



  5. Filtering Ranges with BETWEEN
    6. SELECT * FROM employees
WHERE salary BETWEEN 40000 AND 60000;



  6. Filtering by a Set with IN
    7. SELECT * FROM employees
WHERE department IN ('HR', 'IT', 'Finance');



  7. Pattern Matching with LIKE and ILIKE
    8. SELECT * FROM employees
WHERE last_name LIKE 'S%';



  8. Filtering NULL Values
    9. SELECT * FROM employees
WHERE department IS NULL;

SELECT * FROM employees
WHERE department IS NOT NULL;



  9. Checking Membership with EXISTS
    10. SELECT *
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE d.id = e.department_id
);



  10. Using ANY and ALL
    11. SELECT * FROM employees
WHERE salary > ANY (SELECT salary FROM interns);

SELECT * FROM employees
WHERE salary > ALL (SELECT salary FROM interns);



  11. Combining Multiple Conditions
    12. SELECT * FROM employees
WHERE department = 'Finance'
    AND (salary BETWEEN 40000 AND 60000)
    AND active = TRUE
    AND last_name ILIKE 'J%';



Database Design in PostgreSQL

  1. Introduction



  2. Step 1: Identify Entities and Attributes

    3. 
Entity: Customer
 ├── customer_id (primary key)
 ├── name
 ├── email
 └── phone

Entity: Order
 ├── order_id (primary key)
 ├── customer_id (foreign key)
 ├── order_date
 └── total_amount



  3. Step 2: Define Primary Keys

    4. CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT UNIQUE
);



  4. Step 3: Define Relationships with Foreign Keys

    5. CREATE TABLE orders (
    order_id     SERIAL PRIMARY KEY,
    customer_id  INT REFERENCES customers(customer_id),
    order_date   DATE NOT NULL DEFAULT CURRENT_DATE,
    total_amount NUMERIC(10,2)
);



  5. Step 4: Normalize Your Data



  6. Step 5: Add Constraints and Data Integrity Rules

    7. CREATE TABLE employees (
    id        SERIAL PRIMARY KEY,
    name      TEXT NOT NULL,
    salary    NUMERIC(8,2) CHECK (salary >= 0),
    active    BOOLEAN DEFAULT TRUE
);



  7. Step 6: Use Indexes for Performance

    8. CREATE INDEX idx_employees_department ON employees(department);



  8. Step 7: Consider Denormalization (for Performance)



  9. Step 8: Plan for Relationships — One-to-Many and Many-to-Many



  10. Step 9: Naming Conventions and Documentation



Primary Keys in PostgreSQL

  1. Introduction



  2. Basic Syntax
    3. CREATE TABLE table_name (
    column_name data_type PRIMARY KEY,
    other_column data_type
);



  3. Example: Simple Primary Key
    4. CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name  TEXT NOT NULL,
    last_name   TEXT NOT NULL
);



  4. Using GENERATED AS IDENTITY (Modern SQL)
    5. CREATE TABLE departments (
    dept_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name    TEXT NOT NULL
);



  5. Composite (Multi-Column) Primary Keys
    6. CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id)
);



  6. Adding a Primary Key to an Existing Table
    7. ALTER TABLE employees
ADD CONSTRAINT employees_pkey PRIMARY KEY (employee_id);



  7. Dropping a Primary Key
    8. ALTER TABLE employees
DROP CONSTRAINT employees_pkey;



  8. Primary Key vs. Unique Constraint



  9. Viewing Primary Keys
    10. -- Inside psql
\d employees

-- Query system catalog
SELECT
    tc.table_name,
    kc.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kc
    ON kc.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'PRIMARY KEY';



  10. Behavior with Foreign Keys
    11. CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)
);



  11. Choosing a Good Primary Key



The NOT NULL Constraint in PostgreSQL

  1. Introduction



  2. Basic Syntax
    3. CREATE TABLE table_name (
    column_name data_type NOT NULL,
    ...
);



  3. Example: Defining NOT NULL Columns
    4. CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name  TEXT NOT NULL,
    last_name   TEXT NOT NULL,
    email       TEXT,
    hire_date   DATE NOT NULL DEFAULT CURRENT_DATE
);



  4. Attempting to Insert NULL
    5. INSERT INTO employees (first_name, last_name, hire_date)
VALUES (NULL, 'Smith', '2025-01-01');



  5. Adding a NOT NULL Constraint to an Existing Table
    6. ALTER TABLE employees
ALTER COLUMN email SET NOT NULL;



  6. Removing a NOT NULL Constraint
    7. ALTER TABLE employees
ALTER COLUMN email DROP NOT NULL;



  7. Using DEFAULT with NOT NULL
    8. CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    description TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
);



  8. Checking Which Columns Are NOT NULL
    9. SELECT
    column_name,
    is_nullable
FROM information_schema.columns
WHERE table_name = 'employees';



  9. NOT NULL vs. CHECK Constraints



  10. Behavior with Primary Keys



The DEFAULT Constraint in PostgreSQL

  1. Introduction



  2. Basic Syntax
    3. CREATE TABLE table_name (
    column_name data_type DEFAULT default_value,
    ...
);



  3. Example: Simple DEFAULT Values
    4. CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    description TEXT NOT NULL,
    status TEXT DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);


    INSERT INTO tasks (description) VALUES ('Write documentation');
-- status     → 'pending'
-- created_at → current timestamp


  4. Using DEFAULT with Numeric and Boolean Columns
    5. CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2) DEFAULT 0.00,
    available BOOLEAN DEFAULT TRUE
);



  5. Using DEFAULT with Expressions
    6. CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE DEFAULT CURRENT_DATE,
    total NUMERIC(10,2) DEFAULT (0.0),
    order_ref TEXT DEFAULT ('ORD-' || EXTRACT(YEAR FROM CURRENT_DATE))
);



  6. Inserting Explicit DEFAULT Values
    7. INSERT INTO tasks (description, status)
VALUES ('Review code', DEFAULT);



  7. Adding a DEFAULT Constraint to an Existing Table
    8. ALTER TABLE employees
ALTER COLUMN active SET DEFAULT TRUE;



  8. Removing a DEFAULT Constraint
    9. ALTER TABLE employees
ALTER COLUMN active DROP DEFAULT;



  9. Combining DEFAULT with NOT NULL
    10. CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    active BOOLEAN NOT NULL DEFAULT TRUE
);



  10. Viewing Default Values
    11. SELECT
    column_name,
    column_default
FROM information_schema.columns
WHERE table_name = 'tasks';



The CHECK Constraint in PostgreSQL

  1. Introduction



  2. Basic Syntax
    3. CREATE TABLE table_name (
    column_name data_type CHECK (condition),
    ...
);



  3. Example: Simple CHECK Constraint
    4. CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name  TEXT NOT NULL,
    salary      NUMERIC(10,2) CHECK (salary >= 0)
);



  4. Multiple CHECK Constraints
    5. CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name       TEXT NOT NULL,
    price      NUMERIC(10,2) CHECK (price >= 0),
    discount   NUMERIC(5,2) CHECK (discount BETWEEN 0 AND 100)
);



  5. Table-Level CHECK Constraint
    6. CREATE TABLE accounts (
    account_id   SERIAL PRIMARY KEY,
    balance      NUMERIC(10,2),
    credit_limit NUMERIC(10,2),
    CHECK (balance <= credit_limit)
);



  6. Adding a CHECK Constraint to an Existing Table
    7. ALTER TABLE employees
ADD CONSTRAINT salary_positive CHECK (salary >= 0);



  7. Dropping a CHECK Constraint
    8. ALTER TABLE employees
DROP CONSTRAINT salary_positive;



  8. Using CHECK with Logical Operators
    9. CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    age        INT CHECK (age >= 5 AND age <= 100),
    grade      TEXT CHECK (grade IN ('A', 'B', 'C', 'D', 'F'))
);



  9. Using Functions in CHECK Constraints
    10. CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email   TEXT CHECK (POSITION('@' IN email) > 1)
);



  10. Deferrable CHECK Constraints
    11. ALTER TABLE accounts
ADD CONSTRAINT balance_valid CHECK (balance <= credit_limit)
DEFERRABLE INITIALLY DEFERRED;



  11. Viewing CHECK Constraints
    12. SELECT
    conname AS constraint_name,
    convalidated,
    condeferrable
FROM pg_constraint
WHERE contype = 'c' AND conrelid = 'employees'::regclass;



  12. CHECK vs. Application-Level Validation



Foreign Keys in PostgreSQL

  1. Introduction



  2. Basic Syntax
    3. CREATE TABLE child_table (
    column_name data_type REFERENCES parent_table (parent_column)
);



  3. Example: Basic Foreign Key Relationship
    4. CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name        TEXT NOT NULL
);

CREATE TABLE orders (
    order_id     SERIAL PRIMARY KEY,
    customer_id  INT REFERENCES customers(customer_id),
    order_date   DATE NOT NULL DEFAULT CURRENT_DATE
);



  4. Foreign Key Constraint Naming



  5. Inserting and Deleting Data
    6. INSERT INTO customers (name) VALUES ('Alice');
INSERT INTO orders (customer_id) VALUES (1);  -- valid

INSERT INTO orders (customer_id) VALUES (999); -- invalid



  6. ON DELETE and ON UPDATE Actions




  7. Using SET NULL and SET DEFAULT
    8. CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)
        ON DELETE SET NULL
);




  8. Composite Foreign Keys
    9. CREATE TABLE enrollments (
    student_id INT,
    course_id  INT,
    PRIMARY KEY (student_id, course_id)
);

CREATE TABLE grades (
    student_id INT,
    course_id  INT,
    grade      CHAR(2),
    FOREIGN KEY (student_id, course_id)
        REFERENCES enrollments (student_id, course_id)
        ON DELETE CASCADE
);



  9. Deferrable Foreign Keys
    10. ALTER TABLE orders
ADD CONSTRAINT fk_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id)
    DEFERRABLE INITIALLY DEFERRED;



  10. Checking Existing Foreign Keys
    11. SELECT
    conname             AS constraint_name,
    conrelid::regclass  AS table_name,
    confrelid::regclass AS referenced_table,
    confupdtype         AS on_update,
    confdeltype         AS on_delete
FROM pg_constraint
WHERE contype = 'f';



  11. Behavior When Dropping Referenced Tables
    12. DROP TABLE customers;
-- ERROR:  cannot drop table customers because other objects depend on it



  12. NULL and Foreign Keys



  13. Performance Considerations



Basics on Joining Tables in PostgreSQL

  1. Introduction



  2. Basic JOIN Syntax
    3. SELECT columns
FROM table1
JOIN table2
  ON table1.column_name = table2.column_name;



  3. Example Setup
    4. CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE DEFAULT CURRENT_DATE,
    amount NUMERIC(10,2)
);

INSERT INTO customers (name, city)
VALUES ('Alice', 'Berlin'),
       ('Bob', 'Munich'),
       ('Charlie', 'Hamburg');

INSERT INTO orders (customer_id, amount)
VALUES (1, 120.00),
       (1, 90.50),
       (2, 45.00);



  4. INNER JOIN
    5. SELECT
    customers.name,
    orders.order_id,
    orders.amount
FROM customers
INNER JOIN orders
    ON customers.customer_id = orders.customer_id;



  5. 2. LEFT JOIN (or LEFT OUTER JOIN)
    6. SELECT
    customers.name,
    orders.order_id,
    orders.amount
FROM customers
LEFT JOIN orders
    ON customers.customer_id = orders.customer_id;



  6. RIGHT JOIN (or RIGHT OUTER JOIN)
    7. SELECT
    customers.name,
    orders.order_id,
    orders.amount
FROM customers
RIGHT JOIN orders
    ON customers.customer_id = orders.customer_id;



  7. FULL JOIN (or FULL OUTER JOIN)
    8. SELECT
    customers.name,
    orders.order_id,
    orders.amount
FROM customers
FULL JOIN orders
    ON customers.customer_id = orders.customer_id;



  8. CROSS JOIN
    9. SELECT
    customers.name,
    orders.order_id
FROM customers
CROSS JOIN orders;



  9. SELF JOIN
    10. CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name TEXT,
    manager_id INT REFERENCES employees(employee_id)
);

SELECT e.name AS employee,
       m.name AS manager
FROM employees e
LEFT JOIN employees m
  ON e.manager_id = m.employee_id;



  10. USING Clause
    11. SELECT *
FROM customers
JOIN orders USING (customer_id);



  11. 8. Joining More Than Two Tables
    12. CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_name TEXT,
    quantity INT
);

SELECT
    c.name AS customer,
    o.order_id,
    i.product_name,
    i.quantity
FROM customers c
JOIN orders o
  ON c.customer_id = o.customer_id
JOIN order_items i
  ON o.order_id = i.order_id;



  12. Filtering After Joins
    13. SELECT
    c.name, o.amount
FROM customers c
JOIN orders o
  ON c.customer_id = o.customer_id
WHERE o.amount > 50;



  13. NATURAL JOIN
    14. SELECT *
FROM customers
NATURAL JOIN orders;



  14. Performance Considerations

    15. EXPLAIN SELECT * FROM customers JOIN orders ON customers.customer_id = orders.customer_id;



Set Operations in PostgreSQL

  1. Introduction



  2. Example Setup
    3. CREATE TABLE students_2024 (
    name TEXT,
    city TEXT
);

CREATE TABLE students_2025 (
    name TEXT,
    city TEXT
);

INSERT INTO students_2024 VALUES
    ('Alice', 'Berlin'),
    ('Bob', 'Munich'),
    ('Charlie', 'Hamburg');

INSERT INTO students_2025 VALUES
    ('Alice', 'Berlin'),
    ('David', 'Stuttgart'),
    ('Ella', 'Munich');



  3. 1. UNION — Combine and Remove Duplicates
    4. SELECT * FROM students_2024
UNION
SELECT * FROM students_2025;



  4. 2. UNION ALL — Combine and Keep Duplicates
    5. SELECT * FROM students_2024
UNION ALL
SELECT * FROM students_2025;



  5. 3. INTERSECT — Common Rows Only
    6. SELECT * FROM students_2024
INTERSECT
SELECT * FROM students_2025;



  6. 4. INTERSECT ALL — Common Rows, Keep Duplicates
    7. SELECT * FROM students_2024
INTERSECT ALL
SELECT * FROM students_2025;



  7. 5. EXCEPT — Rows in First Query but Not in Second
    8. SELECT * FROM students_2024
EXCEPT
SELECT * FROM students_2025;



  8. 6. EXCEPT ALL — Keep Duplicates
    9. SELECT * FROM students_2024
EXCEPT ALL
SELECT * FROM students_2025;



  9. 7. Matching Columns and Types
    10. -- This will fail:
SELECT name FROM students_2024
UNION
SELECT name, city FROM students_2025;



  10. 8. Sorting Combined Results
    11. SELECT name, city FROM students_2024
UNION
SELECT name, city FROM students_2025
ORDER BY name;



  11. 9. Using Parentheses for Complex Set Operations
    12. (SELECT * FROM students_2024
 UNION
 SELECT * FROM students_2025)
EXCEPT
SELECT * FROM expelled_students;



  12. 10. Combining with WHERE or JOIN Clauses
    13. SELECT name, city FROM students_2024 WHERE city = 'Munich'
UNION
SELECT name, city FROM students_2025 WHERE city = 'Munich';



  13. 11. Performance Considerations

    14. EXPLAIN SELECT * FROM students_2024 UNION SELECT * FROM students_2025;



The BOOLEAN Type in PostgreSQL

  1. Introduction



  2. Declaration Syntax
    3. CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name    TEXT NOT NULL,
    active  BOOLEAN DEFAULT TRUE
);



  3. Accepted Literal Values



  4. Examples of Insertion
    5. INSERT INTO users (name, active)
VALUES
    ('Alice', TRUE),
    ('Bob', 'yes'),
    ('Charlie', 'f'),
    ('Diana', 0);

SELECT * FROM users;



  5. Boolean Display Formats



  6. Using Booleans in Conditions
    7. SELECT * FROM users WHERE active = TRUE;

-- Shorter equivalent:
SELECT * FROM users WHERE active;

-- Opposite:
SELECT * FROM users WHERE NOT active;



  7. NULL with Boolean Columns
    8. INSERT INTO users (name, active) VALUES ('Eve', NULL);

SELECT name,
       CASE
         WHEN active THEN 'True'
         WHEN NOT active THEN 'False'
         ELSE 'Unknown'
       END AS status
FROM users;



  8. Boolean Operators



  9. Boolean Expressions in SELECT
    10. SELECT name,
       (active AND city = 'Berlin') AS active_in_berlin
FROM users;



  10. Indexing Boolean Columns



  11. Converting Between Boolean and Other Types
    12. SELECT CAST(TRUE AS INT);   -- returns 1
SELECT CAST(FALSE AS INT);  -- returns 0

SELECT CAST(1 AS BOOLEAN);  -- returns TRUE
SELECT CAST(0 AS BOOLEAN);  -- returns FALSE



Character Types: CHAR, VARCHAR, and TEXT in PostgreSQL

  1. Introduction



  2. CHAR(n) — Fixed-Length Character Type
    3. CREATE TABLE products_char (
    code CHAR(5),
    name TEXT
);

INSERT INTO products_char VALUES ('AB', 'Widget');
SELECT code, LENGTH(code) FROM products_char;



  3. VARCHAR(n) — Variable-Length with Limit
    4. CREATE TABLE products_varchar (
    code VARCHAR(5),
    name TEXT
);

INSERT INTO products_varchar VALUES ('AB', 'Widget');
INSERT INTO products_varchar VALUES ('ABCDE', 'Gadget');



  4. TEXT — Unlimited-Length Variable Text
    5. CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT
);

INSERT INTO articles (title, content)
VALUES ('Intro to PostgreSQL', 'PostgreSQL is a powerful open-source RDBMS...');



  5. Comparison Between CHAR, VARCHAR, and TEXT

    6. Type Behavior Length Limit Padded? Use Case
    CHAR(n) Fixed-length Yes Yes (spaces) Fixed-size codes, legacy data
    VARCHAR(n) Variable-length, validated Yes No Text with known maximum length (e.g., names)
    TEXT Variable-length, unlimited No No General-purpose text, documents, comments


  6. CHAR vs VARCHAR vs TEXT in Practice
    7. CREATE TABLE demo (
    c1 CHAR(5),
    c2 VARCHAR(5),
    c3 TEXT
);

INSERT INTO demo VALUES ('AB', 'AB', 'AB');
SELECT c1, c2, c3, LENGTH(c1), LENGTH(c2), LENGTH(c3) FROM demo;



  7. Performance and Storage



  8. Trimming and Padding
    9. SELECT 'abc'::CHAR(5) = 'abc'::CHAR(5);  -- TRUE
SELECT 'abc'::CHAR(5) = 'abc';           -- TRUE (ignores padding)
SELECT LENGTH('abc'::CHAR(5));           -- 5



  9. Changing Length or Type
    10. ALTER TABLE products_varchar
ALTER COLUMN code TYPE VARCHAR(10);

ALTER TABLE products_char
ALTER COLUMN code TYPE TEXT;



  10. Function Compatibility
    11. SELECT UPPER(code), LOWER(name)
FROM products_varchar;

SELECT LENGTH(content), LEFT(content, 20)
FROM articles;



  11. Recommended Usage



Integer Types in PostgreSQL

  1. Introduction



  2. Available Integer Types

    3. Type Alias Storage Size Range Typical Usage
    SMALLINT INT2 2 bytes -32,768 to 32,767 Very small numbers, compact storage
    INTEGER INT or INT4 4 bytes -2,147,483,648 to 2,147,483,647 General-purpose integer type (most common)
    BIGINT INT8 8 bytes -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 Very large values (IDs, counts, etc.)



  3. Basic Usage
    4. CREATE TABLE inventory (
    item_id     INT PRIMARY KEY,
    quantity    SMALLINT,
    reorder_min INT,
    reorder_max BIGINT
);

INSERT INTO inventory VALUES (1, 25, 10, 5000000000);



  4. Integer Aliases and Compatibility



  5. Auto-Incrementing Integers
    6. CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name    TEXT NOT NULL
);



  6. Working with Sequences
    7. -- Create a table with SERIAL
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    amount   NUMERIC(10,2)
);

-- Check the associated sequence
\d orders
-- Shows: "order_id" integer DEFAULT nextval('orders_order_id_seq'::regclass)

-- Get the next value manually:
SELECT nextval('orders_order_id_seq');



  7. Arithmetic and Overflow
    8. SELECT 2147483647 + 1;        -- overflow for INTEGER
SELECT 9223372036854775807 + 1;  -- overflow for BIGINT



  8. Type Casting
    9. SELECT '42'::INT;      -- 42
SELECT 3.7::INT;       -- 4 truncated to 3
SELECT 3.7::BIGINT;    -- also truncates fractional part



  9. Comparison and Sorting
    10. SELECT * FROM inventory ORDER BY quantity DESC;



  10. Converting Between Integer Types
    11. SELECT 500::SMALLINT;   -- OK
SELECT 40000::SMALLINT; -- ERROR: smallint out of range



  11. Performance Tips



The DECIMAL Type in PostgreSQL

  1. Introduction



  2. Declaration Syntax
    3. NUMERIC(precision, scale)
-- or
DECIMAL(precision, scale)



  3. Example: Defining DECIMAL Columns
    4. CREATE TABLE transactions (
    id       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount   DECIMAL(10, 2),
    tax_rate NUMERIC(5, 3),
    total    NUMERIC(12, 4)
);

INSERT INTO transactions (amount, tax_rate, total)
VALUES (1234.56, 0.075, 1326.9300);



  4. Precision and Rounding
    5. CREATE TABLE test_round (
    value NUMERIC(6, 2)
);

INSERT INTO test_round VALUES (123.456);  -- Automatically rounded
SELECT * FROM test_round;



  5. Comparison with Floating-Point Types
    6. SELECT
    0.1 + 0.2                                   AS float_sum,
    CAST(0.1 AS NUMERIC) + CAST(0.2 AS NUMERIC) AS numeric_sum;



  6. Default Precision and Scale
    7. CREATE TABLE flexible_numbers (
    value NUMERIC
);

INSERT INTO flexible_numbers VALUES (12345678901234567890.123456789);
SELECT value FROM flexible_numbers;



  7. Arithmetic Operations
    8. SELECT
    amount,
    tax_rate,
    amount * tax_rate AS tax,
    amount + (amount * tax_rate) AS total
FROM transactions;



  8. Rounding Functions
    9. SELECT ROUND(12.3456, 2),  -- 12.35
       TRUNC(12.3456, 2);  -- 12.34



  9. Formatting and Casting
    10. SELECT TO_CHAR(1234.5::NUMERIC(10,2), '9999.99');  -- '1234.50'
SELECT TO_CHAR(9876.5432, '9,999.00');             -- '9,876.54'


    SELECT CAST(12.34 AS INT);              -- 12
SELECT CAST(12.34 AS DOUBLE PRECISION); -- 12.34


  10. Storage and Performance



  11. Practical Use Cases



Date and Time Types in PostgreSQL

  1. Introduction



  2. Available Date and Time Types

    3. Type Description Example Value
    DATE Stores calendar dates (year, month, day) '2025-11-11'
    TIME [ (p) ] Stores time of day (no date, no timezone) '13:45:20'
    TIME [ (p) ] WITH TIME ZONE Stores time of day including timezone offset '13:45:20+01'
    TIMESTAMP [ (p) ] Stores date and time (no timezone) '2025-11-11 13:45:20'
    TIMESTAMP [ (p) ] WITH TIME ZONE Stores date and time with timezone awareness '2025-11-11 13:45:20+01'
    INTERVAL [ fields ] [ (p) ] Represents duration or difference between timestamps '3 days 04:05:06'



  3. DATE Type
    4. CREATE TABLE events (
    id          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title       TEXT,
    event_date  DATE
);

INSERT INTO events (title, event_date)
VALUES
    ('Conference', '2025-12-01'),
    ('Workshop', CURRENT_DATE);



  4. TIME Type
    5. CREATE TABLE shifts (
    employee    TEXT,
    start_time  TIME,
    end_time    TIME
);

INSERT INTO shifts
VALUES
    ('Alice', '09:00', '17:30'),
    ('Bob',   '22:00', '06:00');



  5. TIME WITH TIME ZONE (TIMETZ)
    6. CREATE TABLE broadcasts (
    show_name TEXT,
    air_time  TIME WITH TIME ZONE
);

INSERT INTO broadcasts
VALUES
    ('Morning News', '07:00+01'),
    ('Night Talk', '23:00-05');



  6. TIMESTAMP (Without Time Zone)
    7. CREATE TABLE logs (
    log_id      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    message     TEXT,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO logs (message) VALUES ('System started');



  7. TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ)
    8. CREATE TABLE meetings (
    meeting_id  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title       TEXT,
    start_at    TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO meetings (title, start_at) VALUES ('Team Sync', '2025-11-11 09:00:00+01');


    SET TIMEZONE TO 'UTC';
SELECT start_at FROM meetings;

SET TIMEZONE TO 'America/New_York';
SELECT start_at FROM meetings;



  8. INTERVAL Type
    9. CREATE TABLE rentals (
    rental_id  SERIAL PRIMARY KEY,
    start_date TIMESTAMP,
    duration   INTERVAL
);

INSERT INTO rentals VALUES (1, '2025-11-11 10:00:00', INTERVAL '3 days 4 hours');

SELECT
    start_date + duration AS return_date
FROM rentals;



  9. Extracting Date and Time Parts
    10. SELECT
    EXTRACT(YEAR FROM start_at)   AS year,
    EXTRACT(MONTH FROM start_at)  AS month,
    EXTRACT(DAY FROM start_at)    AS day,
    EXTRACT(HOUR FROM start_at)   AS hour
FROM meetings;



  10. Date and Time Arithmetic
    11. SELECT
    NOW()                           AS now,
    NOW() + INTERVAL '7 days'       AS next_week,
    NOW() - INTERVAL '1 month'      AS last_month,
    AGE('2025-12-31', '2025-01-01') AS difference;



  11. Useful Built-in Functions

    12. Function Description
    NOW() Current date and time (same as CURRENT_TIMESTAMP)
    CURRENT_DATE Current date (no time)
    CURRENT_TIME Current time of day
    LOCALTIME / LOCALTIMESTAMP Local time/date-time without timezone conversion
    AGE(timestamp1, timestamp2) Difference between two timestamps as interval
    DATE_TRUNC('field', source) Truncates a timestamp to the specified precision (e.g., hour, day, month) 
    SELECT DATE_TRUNC('month', NOW()); -- first day of current month


  12. Converting Between Date/Time Types
    13. SELECT
    '2025-11-11'::DATE + INTERVAL '1 day',   -- date → timestamp
    '13:45:00'::TIME + INTERVAL '2 hours',   -- time + interval
    NOW()::DATE AS current_day,              -- timestamp → date
    CAST('2025-11-11 09:00' AS TIMESTAMPTZ);


The UUID Type in PostgreSQL

  1. Introduction



  2. UUID Format



  3. Creating a Table with UUID Column
    4. CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    name    TEXT NOT NULL
);

INSERT INTO users (user_id, name)
VALUES ('550e8400-e29b-41d4-a716-446655440000', 'Alice');



  4. Generating UUIDs Automatically



  5. Auto-Generated UUIDs as Primary Keys
    6. CREATE TABLE api_tokens (
    token_id  UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id   UUID NOT NULL,
    issued_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO api_tokens (user_id)
VALUES ('550e8400-e29b-41d4-a716-446655440000');



  6. UUID Versions Overview

    7. Version Source Function (uuid-ossp) Use Case
    v1 Timestamp + MAC address uuid_generate_v1() Predictable, contains machine/time info (not recommended for public use)
    v3 Namespace + MD5 hash uuid_generate_v3(namespace, name) Deterministic generation from names
    v4 Random numbers uuid_generate_v4() or gen_random_uuid() Most common, fast, and secure for general use
    v5 Namespace + SHA-1 hash uuid_generate_v5(namespace, name) Deterministic but safer than v3



  7. Comparing UUID vs SERIAL IDs

    8. Aspect SERIAL / IDENTITY UUID
    Uniqueness Unique within a single database sequence Globally unique across databases and servers
    Predictability Sequential, reveals insertion order Non-sequential, unpredictable
    Storage Size 4–8 bytes 16 bytes
    Indexing Speed Faster (smaller, sequential) Slightly slower (larger, random order)
    Replication / Merging May collide between instances Safe across distributed databases
    Use Case Simple local applications Microservices, APIs, distributed systems



  8. Querying UUID Columns
    9. SELECT * FROM api_tokens
WHERE token_id = 'b36d6d3e-b41a-4d0c-b4e4-9985a93f79a7';

-- You can also use functions:
SELECT token_id::TEXT FROM api_tokens;



  9. Indexing and Performance
    10. CREATE INDEX idx_api_tokens_user_id ON api_tokens(user_id);



Common Table Expressions (CTE) in PostgreSQL

  1. Introduction



  2. Basic Syntax
    3. WITH cte_name (optional_columns) AS (
    SQL_query
)
SELECT ...
FROM cte_name;



  3. Example: Basic Non-Recursive CTE
    4. WITH top_sellers AS (
    SELECT
        seller_id,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY seller_id
    HAVING SUM(amount) > 5000
)

SELECT
    s.seller_id,
    s.total_sales,
    u.name
FROM top_sellers AS s
JOIN users AS u ON u.id = s.seller_id;



  4. Using Multiple CTEs
    5. WITH
    orders_per_customer AS (
        SELECT customer_id, COUNT(*) AS order_count
        FROM orders
        GROUP BY customer_id
    ),
    top_customers AS (
        SELECT customer_id
        FROM orders_per_customer
        WHERE order_count > 10
    )

SELECT c.name, opc.order_count
FROM top_customers tc
JOIN orders_per_customer opc ON tc.customer_id = opc.customer_id
JOIN customers c ON c.id = tc.customer_id;



  5. Modifying Data with CTEs
    6. WITH updated AS (
    UPDATE products
    SET price = price * 1.10
    WHERE category = 'electronics'
    RETURNING id, price
)
SELECT * FROM updated;



  6. Recursive CTEs

    7. WITH RECURSIVE subordinates AS (
    -- Anchor: start from the manager
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step: find employees reporting to those above
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;



  7. Example: Folder Hierarchy
    8. CREATE TABLE folders (
    id SERIAL PRIMARY KEY,
    name TEXT,
    parent_id INT REFERENCES folders(id)
);

WITH RECURSIVE folder_tree AS (
    SELECT id, name, parent_id, 1 AS depth
    FROM folders
    WHERE parent_id IS NULL

    UNION ALL

    SELECT f.id, f.name, f.parent_id, ft.depth + 1
    FROM folders f
    JOIN folder_tree ft ON f.parent_id = ft.id
)
SELECT * FROM folder_tree ORDER BY depth;



  8. Performance Considerations



Views in PostgreSQL

  1. Introduction



  2. Basic Syntax
    3. CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;



  3. Example: Creating a Simple View
    4. CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE is_active = true;

SELECT * FROM active_users;



  4. Using Views to Simplify Complex Queries
    5. CREATE VIEW monthly_sales AS
SELECT
    seller_id,
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount)                     AS total_sales
FROM orders
GROUP BY seller_id, month;

SELECT * FROM monthly_sales
WHERE total_sales > 5000;



  5. Updating Data Through Views

    6. CREATE VIEW user_public_info AS
SELECT id, name, email
FROM users;

UPDATE user_public_info
SET name = 'Alice Cooper'
WHERE id = 5;



  6. Security and Access Control

    7. CREATE VIEW user_safe_view AS
SELECT id, name
FROM users;

GRANT SELECT ON user_safe_view TO analyst_role;
REVOKE SELECT ON users FROM analyst_role;



  7. Replacing or Dropping a View
    8. DROP VIEW IF EXISTS view_name;

CREATE OR REPLACE VIEW view_name AS
SELECT ...
FROM ...;



  8. Materialized Views

    9. CREATE MATERIALIZED VIEW sales_summary AS
SELECT seller_id, SUM(amount) AS total_sales
FROM orders
GROUP BY seller_id;

-- Read from the materialized view
SELECT * FROM sales_summary;

-- Refresh when data changes:
REFRESH MATERIALIZED VIEW sales_summary;



  9. Materialized View with Indexes
    10. CREATE MATERIALIZED VIEW monthly_stats AS
SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*)                        AS total_users
FROM users
GROUP BY month;

CREATE INDEX idx_monthly_stats_month ON monthly_stats(month);



  10. Advanced: Refreshing Concurrently

    11. REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;



Materialized Views in PostgreSQL

  1. Introduction



  2. Basic Syntax
    3. CREATE MATERIALIZED VIEW view_name AS
SELECT ...
FROM ...
WHERE ...;



  3. Example: Creating a Materialized View
    4. CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    seller_id,
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount)                     AS total_sales
FROM orders
GROUP BY seller_id, month;

SELECT * FROM monthly_sales;



  4. Refreshing Materialized Views

    5. REFRESH MATERIALIZED VIEW monthly_sales;



  5. Refreshing Concurrently (Non-blocking)

    6. REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;


    CREATE UNIQUE INDEX idx_monthly_sales_unique ON monthly_sales(seller_id, month);



  6. Materialized Views vs Normal Views

    7. Feature Normal View Materialized View
    Storage No physical storage Stores data physically
    Performance Runs query every time (slow for heavy logic) Fast reads, cached data
    Freshness Always up-to-date Must be refreshed manually
    Use Case Simple abstractions Heavy queries, analytics
    Blocking No refresh needed Refresh blocks reads unless CONCURRENTLY


  7. Materialized Views with Indexes
    8. CREATE MATERIALIZED VIEW user_summary AS
SELECT
    country,
    COUNT(*) AS user_count
FROM users
GROUP BY country;

CREATE INDEX idx_user_summary_country
ON user_summary(country);



  8. Manually Refreshing Only When Needed



  9. Dropping a Materialized View
    10. DROP MATERIALIZED VIEW IF EXISTS view_name;



  10. Advanced: Materialized Views with Joins
    11. CREATE MATERIALIZED VIEW product_stats AS
SELECT
    p.id AS product_id,
    p.name,
    COUNT(o.id) AS orders_count,
    SUM(o.amount) AS total_revenue
FROM products p
LEFT JOIN orders o ON o.product_id = p.id
GROUP BY p.id, p.name;

-- Refresh when needed
REFRESH MATERIALIZED VIEW product_stats;



  11. Advanced: Materialized View with WHERE Filters
    12. CREATE MATERIALIZED VIEW recent_orders AS
SELECT *
FROM orders
WHERE order_date > NOW() - INTERVAL '30 days';



The CASE Expression in PostgreSQL

  1. Introduction



  2. General Syntax
    3. CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END



  3. Simple CASE Expression

    4. CASE status
    WHEN 'A' THEN 'Active'
    WHEN 'I' THEN 'Inactive'
    ELSE 'Unknown'
END



  4. Searched CASE Expression

    5. CASE
    WHEN amount > 1000 THEN 'High'
    WHEN amount BETWEEN 500 AND 1000 THEN 'Medium'
    WHEN amount > 0 THEN 'Low'
    ELSE 'None'
END



  5. Example: Categorizing Orders
    6. SELECT
    order_id,
    amount,
    CASE
        WHEN amount > 1000 THEN 'VIP Order'
        WHEN amount > 100 THEN 'Standard Order'
        ELSE 'Small Order'
    END AS order_type
FROM orders;



  6. Using CASE in ORDER BY
    7. SELECT name, role
FROM employees
ORDER BY
    CASE
        WHEN role = 'manager'   THEN 1
        WHEN role = 'developer' THEN 2
        ELSE 3
    END;



  7. Using CASE in Aggregation
    8. SELECT
    seller_id,
    SUM(CASE WHEN amount > 500 THEN 1 ELSE 0 END)  AS large_orders,
    SUM(CASE WHEN amount <= 500 THEN 1 ELSE 0 END) AS small_orders
FROM orders
GROUP BY seller_id;



  8. Handling NULL with CASE
    9. SELECT
    name,
    CASE
        WHEN phone IS NULL THEN 'No phone'
        ELSE phone
    END AS phone_display
FROM users;



  9. CASE with Multiple Conditions
    10. SELECT
    product,
    stock,
    CASE
        WHEN stock = 0   THEN 'Out of Stock'
        WHEN stock <= 10 THEN 'Low Stock'
        WHEN stock <= 50 THEN 'In Stock'
        ELSE 'High Stock'
    END AS stock_level
FROM products;



  10. Using CASE to Avoid Division by Zero
    11. SELECT
    seller_id,
    total_sales,
    total_orders,
    CASE
        WHEN total_orders = 0 THEN 0
        ELSE total_sales / total_orders
    END AS avg_order_value
FROM seller_stats;



  11. Nested CASE Expressions
    12. SELECT
    name,
    salary,
    CASE
        WHEN salary > 70000 THEN 'Senior'
        WHEN salary > 40000 THEN
            CASE
                WHEN salary > 55000 THEN 'Mid-High'
                ELSE 'Mid'
            END
        ELSE 'Junior'
    END AS level
FROM employees;



  12. CASE Expression Without ELSE
    13. CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
END



  13. Performance Considerations



The COALESCE Function in PostgreSQL

  1. Introduction



  2. Syntax
    3. COALESCE(value1, value2, value3, ...)



  3. Basic Example
    4. SELECT COALESCE(NULL, NULL, 'Hello', 'World');



  4. Using COALESCE to Replace NULL With a Default
    5. SELECT
    name,
    COALESCE(phone, 'No phone number') AS phone_display
FROM users;



  5. Using COALESCE With Numeric Values
    6. SELECT
    price,
    COALESCE(discount_price, price) AS effective_price
FROM products;



  6. Using COALESCE in Calculations
    7. SELECT
    COALESCE(total_sales, 0) / COALESCE(order_count, 1) AS avg_order_value
FROM seller_stats;



  7. COALESCE With Multiple Alternatives
    8. SELECT
    COALESCE(username, email, phone, 'Unknown User') AS identifier
FROM users;



  8. COALESCE vs CASE Expression
    9. CASE
    WHEN value1 IS NOT NULL THEN value1
    WHEN value2 IS NOT NULL THEN value2
    ELSE value3
END


    COALESCE(value1, value2, value3)



  9. Using COALESCE With Aggregations
    10. SELECT
    seller_id,
    COALESCE(SUM(amount), 0) AS total_sales
FROM orders
GROUP BY seller_id;



  10. Handling NULL Dates
    11. SELECT
    name,
    COALESCE(last_login, NOW()) AS effective_last_login
FROM users;



  11. COALESCE in ORDER BY
    12. SELECT title
FROM books
ORDER BY COALESCE(published_year, 0);



  12. COALESCE in JOIN Conditions
    13. SELECT *
FROM orders o
JOIN customers c
    ON COALESCE(o.customer_id, 0) = c.id;



  13. COALESCE vs NULLIF

    14. SELECT
    COALESCE(NULLIF(status, ''), 'unknown') AS clean_status
FROM tasks;



  14. Performance Considerations



The NULLIF Function in PostgreSQL

  1. Introduction



  2. Syntax
    3. NULLIF(value1, value2)



  3. Basic Example
    4. SELECT NULLIF(10, 10);   -- returns NULL
SELECT NULLIF(10, 5);    -- returns 10



  4. Avoiding Division by Zero

    5. SELECT
    total_sales / NULLIF(total_orders, 0) AS avg_order_value
FROM seller_stats;


    SELECT
    COALESCE(total_sales / NULLIF(total_orders, 0), 0) AS avg_order_value
FROM seller_stats;



  5. Cleaning Up Placeholder Values

    6. SELECT
    NULLIF(phone, '') AS clean_phone
FROM users;



  6. Another Example: Converting "unknown" Strings to NULL
    7. SELECT
    NULLIF(status, 'unknown') AS clean_status
FROM tasks;



  7. NULLIF with Numeric Data
    8. SELECT
    price,
    discount,
    NULLIF(discount, 0) AS valid_discount
FROM products;



  8. NULLIF Combined with COALESCE

    9. SELECT
    COALESCE(NULLIF(username, ''), 'Guest') AS display_name
FROM accounts;



  9. NULLIF in ORDER BY
    10. SELECT name
FROM customers
ORDER BY NULLIF(priority, 0);



  10. NULLIF Inside Aggregations
    11. SELECT
    region,
    SUM(NULLIF(amount, 0)) AS total_revenue
FROM sales
GROUP BY region;



  11. NULLIF and Type Casting
    12. SELECT NULLIF(value::INT, 0)
FROM data;



  12. NULLIF with Multiple Comparisons (Using CASE Instead)

    13. CASE
    WHEN score IN (0, -1, -99) THEN NULL
    ELSE score
END



  13. Performance Considerations



The LEAST and GREATEST Functions in PostgreSQL

  1. Introduction



  2. Syntax
    3. LEAST(value1, value2, value3, ...)
GREATEST(value1, value2, value3, ...)



  3. Basic Examples
    4. SELECT LEAST(5, 8, 2, 7);       -- returns 2
SELECT GREATEST(5, 8, 2, 7);    -- returns 8



  4. Using LEAST and GREATEST With Text
    5. SELECT LEAST('banana', 'apple', 'cherry');    -- returns 'apple'
SELECT GREATEST('banana', 'apple', 'cherry'); -- returns 'cherry'



  5. Using LEAST and GREATEST With Dates
    6. SELECT LEAST('2024-01-01', '2023-12-31');    -- 2023-12-31
SELECT GREATEST('2024-01-01', '2023-12-31'); -- 2024-01-01



  6. Handling NULL Values
    7. SELECT LEAST(10, NULL, 5);      -- NULL
SELECT GREATEST(NULL, 100, 50); -- NULL


    SELECT LEAST(COALESCE(a, 0), COALESCE(b, 0)) FROM table;


  7. Clamping Values (Setting Min/Max Boundaries)

    8. -- Clamp score to range 0–100
SELECT LEAST(100, GREATEST(score, 0)) AS clamped_score
FROM results;



  8. Using LEAST / GREATEST for Conditional Logic

    9. SELECT
    salary,
    GREATEST(salary, 2000) AS minimum_salary
FROM employees;



  9. Choosing the Earliest or Latest Timestamp
    10. SELECT
    LEAST(start_time, updated_at, created_at) AS earliest_timestamp,
    GREATEST(start_time, updated_at, created_at) AS latest_timestamp
FROM logs;



  10. Comparing Columns Across Rows
    11. SELECT
    product,
    LEAST(price_usd, price_eur, price_gbp) AS minimum_price,
    GREATEST(price_usd, price_eur, price_gbp) AS maximum_price
FROM products;



  11. LEAST/GREATEST With Derived Expressions
    12. SELECT
    id,
    LEAST(amount * 0.9, amount - 10) AS discounted_price
FROM orders;



  12. Using LEAST and GREATEST in ORDER BY
    13. SELECT name
FROM employees
ORDER BY GREATEST(salary, bonus);



  13. Performance Considerations



Details on the LIKE Operator in PostgreSQL

  1. Introduction



  2. Syntax
    3. <expression> LIKE <pattern>
<expression> NOT LIKE <pattern>
<expression> ILIKE <pattern>
<expression> NOT ILIKE <pattern>



  3. Wildcard Rules: % and _
    4. -- % wildcard (zero or more characters)
SELECT 'postgresql' LIKE 'post%';   -- TRUE

-- _ wildcard (exactly one character)
SELECT 'cat' LIKE 'c_t';            -- TRUE



  4. Advanced: Matching Multi-byte Unicode Characters

    5. SELECT '你' LIKE '_';    -- TRUE (one Chinese character)
SELECT '你' LIKE '__';   -- FALSE



  5. Escaping Wildcards: The ESCAPE Clause

    6. SELECT '100% match' LIKE '100\% match' ESCAPE '\';   -- TRUE
SELECT 'file_1'     LIKE 'file\_'      ESCAPE '\';   -- TRUE


    SELECT 'file_1' LIKE 'file#_' ESCAPE '#';            -- TRUE



  6. Trailing Spaces and LIKE

    7. SELECT 'abc ' LIKE 'abc';     -- FALSE
SELECT 'abc ' LIKE 'abc%';    -- TRUE



  7. Locale and Collation Impact

    8. SELECT 'äpfel' LIKE 'a%';    -- depends on collation



  8. LIKE vs ILIKE (Case-Insensitive Patterns)
    9. SELECT 'Hello' LIKE  'h%';   -- FALSE
SELECT 'Hello' ILIKE 'h%';   -- TRUE



  9. LIKE and NULL
    10. SELECT NULL LIKE '%';    -- NULL
SELECT 'abc' LIKE NULL;  -- NULL



  10. Using LIKE With ANY / ALL
    11. SELECT *
FROM logs
WHERE message LIKE ANY (ARRAY['%error%', '%fail%', '%fatal%']);


  11. LIKE vs SIMILAR TO vs Regular Expressions


    12. -- LIKE (simple)
WHERE name LIKE 'A%';

-- SIMILAR TO (regex with SQL syntax)
WHERE name SIMILAR TO '(A|B)%';

-- POSIX regex (preferred for complex patterns)
WHERE name ~ '^(A|B).*';



  12. Index Usage Rules

    13. -- Index used
WHERE name LIKE 'abc%';


    WHERE name LIKE '%abc';
WHERE name LIKE '%abc%';
WHERE name LIKE '_bc%';             -- cannot use index
WHERE LOWER(name) LIKE 'abc%';      -- no index unless functional index exists



  13. Using pg_trgm for Fast LIKE Searches

    14. CREATE EXTENSION IF NOT EXISTS pg_trgm;


    CREATE INDEX idx_users_name_trgm
ON users USING gin (name gin_trgm_ops);



  14. Performance Considerations



  15. Practical Examples

    16. -- Emails from Gmail
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Names containing digits
SELECT * FROM users WHERE name LIKE '%_%' ESCAPE '\\';

-- Case-insensitive search
SELECT * FROM cities WHERE name ILIKE 'ber%';

-- Titles that start with a letter A–F
SELECT * FROM books WHERE title ~ '^[A-F]';



PostgreSQL Extensions

  1. Introduction



  2. Concept: Installed vs. Available Extensions

    3. SELECT * FROM pg_available_extensions;


    SELECT * FROM pg_extension;


  3. Creating an Extension (Enabling It in a Database)
    4. CREATE EXTENSION pg_trgm;


    CREATE EXTENSION pg_trgm WITH SCHEMA extensions;



  4. Listing Extensions and Their Versions
    5. -- Installed in current database
SELECT
    extname,
    extversion,
    extnamespace::regnamespace AS schema_name
FROM
    pg_extension
ORDER BY
    extname;

-- Available (but maybe not installed)
SELECT
    name,
    default_version,
    installed_version
FROM
    pg_available_extensions
ORDER BY
    name;



  5. Extension Files and Control Files (High-Level)



  6. Extension Schemas and search_path

    7. CREATE SCHEMA IF NOT EXISTS extensions;

CREATE EXTENSION IF NOT EXISTS hstore
    WITH SCHEMA extensions;



  7. Updating Extensions (ALTER EXTENSION)
    8. -- Update a single extension to latest version
ALTER EXTENSION pg_trgm UPDATE;

-- Update to a specific version (if supported)
ALTER EXTENSION some_ext UPDATE TO '1.2';


  8. Removing Extensions (DROP EXTENSION)
    9. DROP EXTENSION pg_trgm;


    DROP EXTENSION pg_trgm CASCADE;



  9. Common Built-In Extensions



  10. Extensions Requiring shared_preload_libraries

    11. -- In postgresql.conf (example)
shared_preload_libraries = 'pg_stat_statements'

-- After server restart, in each database where you want it:
CREATE EXTENSION pg_stat_statements;



  11. Permissions and Security



  12. Extensions and Backups



  13. Checking Dependencies Between Extensions and Objects

    14. SELECT
    objid::regclass,
    classid::regclass
FROM pg_depend d
JOIN pg_extension e ON d.refobjid = e.oid
WHERE e.extname = 'pg_trgm';



  14. Practical Workflow Example

    15. -- 1. Create a dedicated schema for extensions (optional)
CREATE SCHEMA IF NOT EXISTS extensions;

-- 2. Install the pg_trgm extension into that schema
CREATE EXTENSION IF NOT EXISTS pg_trgm
WITH SCHEMA extensions;

-- 3. Create a trigram index for LIKE / ILIKE searches
CREATE INDEX idx_users_name_trgm
ON users
USING gin (name extensions.gin_trgm_ops);

-- 4. Now queries like this become much faster:
SELECT *
FROM users
WHERE name ILIKE '%wang%';


Creating Your Own PostgreSQL Extensions

  1. Introduction



  2. High-Level Steps



  3. Directory Locations

    4. pg_config --sharedir   # usually contains share/extension
pg_config --pkglibdir  # usually contains shared libraries (.so/.dll)



  4. Creating a Simple SQL-only Extension: Example

    5. -- File: hello_ext--1.0.sql
CREATE OR REPLACE FUNCTION hello(name text)
RETURNS text
LANGUAGE sql
AS
$$
    SELECT 'Hello, ' || name || '!'::text;
$$;



  5. Writing the Control File (.control)

    6. # File: hello_ext.control
comment = 'Simple hello extension example'
default_version = '1.0'
relocatable = true
schema = public
requires = ''



  6. Installing the Files

    7. cp hello_ext.control   $(pg_config --sharedir)/extension/
cp hello_ext--1.0.sql  $(pg_config --sharedir)/extension/



  7. Creating the Extension in a Database

    8. CREATE EXTENSION hello_ext;


    SELECT hello('Hwangfucius');
-- Result: 'Hello, Hwangfucius!'


    CREATE SCHEMA IF NOT EXISTS extensions;
CREATE EXTENSION hello_ext WITH SCHEMA extensions;


  8. Versioning and Upgrade Scripts

    9. # File: hello_ext.control (updated)
comment = 'Simple hello extension example'
default_version = '1.1'
relocatable = true
schema = public
requires = ''

    -- File: hello_ext--1.0--1.1.sql
-- Migration from 1.0 to 1.1

CREATE OR REPLACE FUNCTION hello_upper(name text)
RETURNS text
LANGUAGE sql
AS
$$
    SELECT upper(hello(name));
$$;


    ALTER EXTENSION hello_ext UPDATE TO '1.1';



  9. C-based Extensions (Overview Only)

    10. hello_ext/
  hello_ext.c
  hello_ext.control
  hello_ext--1.0.sql
  Makefile


    /* hello_ext.c (simplified example) */
#include "postgres.h"
#include "fmgr.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(hello_c);

Datum
hello_c(PG_FUNCTION_ARGS)
{
    text *name = PG_GETARG_TEXT_PP(0);
    text *prefix = cstring_to_text("Hello, ");
    text *result = catenate_text(prefix, name); /* pseudo helper */
    PG_RETURN_TEXT_P(result);
}


    -- hello_ext--1.0.sql
CREATE FUNCTION hello_c(name text)
RETURNS text
AS 'hello_ext', 'hello_c'
LANGUAGE c
STRICT;



  10. Relocatable vs Non-Relocatable Extensions

    11. ALTER EXTENSION hello_ext SET SCHEMA extensions;



  11. Dependencies Between Extensions

    12. # File: my_ext.control
comment = 'My extension using pg_trgm'
default_version = '1.0'
relocatable = true
requires = 'pg_trgm'



  12. Testing and Debugging Your Extension

    13. CREATE DATABASE ext_test;
\c ext_test

CREATE EXTENSION hello_ext;
SELECT hello('Test');



Regular Expressions in PostgreSQL

  1. Introduction



  2. Regex Matching Operators

    3. OperatorDescriptionCase Sensitivity
    ~String matches regular expressionSensitive
    ~*String matches regular expressionInsensitive
    !~String does NOT match regular expressionSensitive
    !~*String does NOT match regular expressionInsensitive
    SELECT 'Hello123' ~ '^[A-Za-z]+$';   -- FALSE
SELECT 'Hello'    ~ '^[A-Za-z]+$';   -- TRUE
SELECT 'hello'    ~* 'HELLO';        -- TRUE (case-insensitive)


  3. Basic Regex Syntax

    4. SELECT 'abc123' ~ '^[a-z]+[0-9]+$';  -- TRUE
SELECT 'cat'    ~ 'c(at|ut)';         -- TRUE
SELECT 'cut'    ~ 'c(at|ut)';         -- TRUE


  4. Character Classes

    5. SELECT '5'   ~ '^[0-9]$';       -- TRUE
SELECT 'g'   ~ '^[^0-9]$';      -- TRUE
SELECT 'ü'   ~ '^[A-Za-z]$';    -- FALSE (ASCII only!)



  5. POSIX Character Classes (Locale-Aware)

    6. ClassDescription
    [:alpha:]Letters (locale aware)
    [:digit:]Digits
    [:alnum:]Letters and digits
    [:space:]Whitespace
    [:lower:]Lowercase letters
    [:upper:]Uppercase letters
    SELECT 'ä' ~ '^[[:alpha:]]+$';   -- TRUE (depends on locale)



  6. Anchors

    7. SELECT 'postgresql' ~ '^post';     -- TRUE
SELECT 'postgresql' ~ 'sql$';      -- TRUE
SELECT 'cat dog'    ~ '\bdog\b';   -- TRUE


  7. Quantifiers

    8. SELECT 'hellooo' ~ 'o{2,}';   -- TRUE
SELECT 'ho'      ~ 'o{2,}';   -- FALSE


  8. Grouping and Alternation

    9. SELECT 'gray'  ~ 'gr(a|e)y';  -- TRUE
SELECT 'grey'  ~ 'gr(a|e)y';  -- TRUE



  9. Using Regex in WHERE Clauses

    10. SELECT *
FROM users
WHERE email ~ '^[a-z0-9._%+-]+@example\.com$';


    WHERE name ~* '^(junzhe|wang)$';


  10. Regex in SELECT (search inside strings)

    11. SELECT
    regexp_matches('abc123xyz', '[0-9]+');



  11. Regex Functions and Operators

    12. FunctionDescription
    regexp_matches()Return all matches as text arrays
    regexp_replace()Find & replace using regex
    regexp_split_to_array()Split text using regex
    regexp_split_to_table()Split into table rows
    SELECT regexp_replace('abc123', '[0-9]', '#', 'g');
-- Result: 'abc###'


  12. Escaping Characters in Regex

    13. SELECT '3+4' ~ '3\+4';     -- match literal +
SELECT 'a.b' ~ 'a\.b';     -- match literal dot



  13. Dollar-Quoted Strings to Avoid Escaping

    14. SELECT '3+4' ~ $re$3\+4$re$;



  14. Extracting Multiple Matches

    15. SELECT regexp_matches(
    'a1 b2 c3',
    '([a-z])([0-9])',
    'g'
);



  15. Performance Considerations

    16. CREATE EXTENSION pg_trgm;

CREATE INDEX idx_users_name_trgm
    ON users USING gin (name gin_trgm_ops);

SELECT * FROM users WHERE name ~ 'wang';



  16. Regex vs LIKE vs SIMILAR TO

    17. FeatureLIKESIMILAR TORegex (~)
    Wildcard powerLowMediumHigh
    Performs complex validationNoSomewhatYes
    Index supportOnly prefixNoNo (except pg_trgm)
    Syntax complexityVery easyMediumHigh



The SIMILAR TO Operator in PostgreSQL

  1. Introduction



  2. Basic Syntax
    3. value SIMILAR TO pattern
value NOT SIMILAR TO pattern



  3. Fundamental Rules of SIMILAR TO

    4. SELECT 'cat' SIMILAR TO '(cat|dog)';   -- TRUE
SELECT 'dog' SIMILAR TO '(cat|dog)';   -- TRUE
SELECT 'frog' SIMILAR TO '(cat|dog)';  -- FALSE


  4. SIMILAR TO vs LIKE

    5. FeatureLIKESIMILAR TO
    AlternationNoYes (|)
    GroupingNoYes
    Character classesNoYes
    Complex validationNoPossible
    Case sensitivitySensitiveSensitive only (no ILIKE equivalent)



  5. Alternation (|) and Grouping

    6. SELECT 'apple'  SIMILAR TO '(apple|banana|cherry)';  -- TRUE
SELECT 'guava'  SIMILAR TO '(apple|banana|cherry)';  -- FALSE



  6. Character Classes

    7. SELECT 'a' SIMILAR TO '[abc]';     -- TRUE
SELECT 'd' SIMILAR TO '[abc]';     -- FALSE
SELECT '5' SIMILAR TO '[0-9]';     -- TRUE



  7. Wildcard Behavior

    8. SELECT 'hello' SIMILAR TO 'h%';    -- TRUE
SELECT 'hello' SIMILAR TO 'h_llo'; -- TRUE


  8. Anchoring Rules (Implicit Anchors)

    9. SELECT 'hello' SIMILAR TO 'ell';    -- FALSE (must match whole string)
SELECT 'hello' SIMILAR TO '%ell%';  -- TRUE


  9. Complex Validation Example

    10. SELECT '2024-10-15' SIMILAR TO '[0-9]{4}-[0-9]{2}-[0-9]{2}';  -- TRUE
SELECT '2024/10/15' SIMILAR TO '[0-9]{4}-[0-9]{2}-[0-9]{2}';  -- FALSE



  10. Using SIMILAR TO With NOT

    11. SELECT 'abc' NOT SIMILAR TO '[0-9]+';   -- TRUE



  11. Escaping Special Characters

    12. SELECT 'a+b' SIMILAR TO 'a\+b';  -- match literal +


    SELECT 'a+b' SIMILAR TO 'a#+' ESCAPE '#';


  12. SIMILAR TO is Fully Anchored

    13. SELECT 'abc'   SIMILAR TO 'a.*';    -- FALSE
SELECT 'abc'   SIMILAR TO 'a%';     -- TRUE



  13. Combining Conditions

    14. SELECT *
FROM logs
WHERE message SIMILAR TO '%(ERROR|WARN|FATAL)%';



  14. SIMILAR TO vs Regex Operators (~)

    15. FeatureSIMILAR TORegex (~)
    Power levelMediumHigh
    PerformanceMediumLower (heavier)
    Anchored matchingAlways full stringOptional
    Unicode supportLimitedFull POSIX regex support
    SyntaxSQL-standardPOSIX regex syntax
    AlternationYesYes
    Complex patternsLimitedVery strong



  15. Performance Considerations

    16. CREATE EXTENSION pg_trgm;

CREATE INDEX idx_logs_msg_trgm
ON logs USING gin (message gin_trgm_ops);

SELECT * FROM logs
WHERE message SIMILAR TO '%fail%';



  16. Practical Examples

    17. -- Validate YYYY-MM-DD
SELECT '2024-12-31' SIMILAR TO '[0-9]{4}-[0-9]{2}-[0-9]{2}';

-- Match emails ending in .com or .net
SELECT 'test@example.com' SIMILAR TO '%@(.*)\.(com|net)';

-- Validate integer or decimal
SELECT '123.45' SIMILAR TO '[0-9]+(\.[0-9]+)?';

-- Match strings starting with A–F
SELECT 'Apple' SIMILAR TO '[A-F]%';



Basics on Full-Text Search (FTS) in PostgreSQL

  1. Introduction



  2. Core Concepts

    3. SELECT 'a fat cat sat on a mat'::tsvector;
-- 'a' 'cat' 'fat' 'mat' 'on' 'sat'

SELECT to_tsvector('English', 'The cars are running');
-- 'car':2 'run':4


  3. tsvector: The Document

    4. SELECT to_tsvector('English', 'Programming program programs programmed');
-- 'programm':1,3,4,5



  4. tsquery: The Search Expression

    5. SELECT to_tsquery('english', 'cat & dog');
-- 'cat' & 'dog'

SELECT to_tsquery('english', 'car | truck');
-- 'car' | 'truck'


  5. Basic Searching with @@ Operator

    6. SELECT
    to_tsvector('english', 'The big car crashed'),
    to_tsquery('english', 'car');


    SELECT
    to_tsvector('english', 'The big car crashed')
        @@ to_tsquery('english', 'car');     -- TRUE


  6. Phrase Search (word adjacency)

    7. SELECT to_tsquery('english', 'big <-> car');


    SELECT to_tsvector('The big car crashed') @@
       to_tsquery('big <-> car');     -- TRUE


  7. Phrase Search (exact phrase) in PostgreSQL ≥ 15

    8. SELECT to_tsvector('The big red car') @@@ 'big red car';
-- TRUE



  8. Indexing Full-Text Search

    9. CREATE INDEX idx_docs_fts
ON documents
USING gin (to_tsvector('english', content));


    SELECT *
FROM documents
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'car');


  9. Storing tsvector in a Column

    10. ALTER TABLE documents
ADD COLUMN fts tsvector
    GENERATED ALWAYS AS (to_tsvector('english', content)) STORED;

CREATE INDEX idx_docs_fts ON documents USING gin (fts);



  10. Composing Queries with plainto_tsquery and websearch_to_tsquery

    11. SELECT websearch_to_tsquery('english', '"big car" -red');
-- 'big' <-> 'car' & !'red'


  11. Ranking Search Results

    12. SELECT
    id,
    ts_rank(fts, to_tsquery('english', 'car')) AS rank
FROM documents
WHERE fts @@ to_tsquery('english', 'car')
ORDER BY rank DESC;


    SELECT setweight(to_tsvector(title), 'A') ||
       setweight(to_tsvector(body),  'D')
       AS fts;


  12. Lexeme Weights

    13. SELECT setweight(to_tsvector('Title text'), 'A') ||
       setweight(to_tsvector('Body text'),  'D');



  13. Stop Words

    14. SELECT to_tsvector('english', 'the the the cat');
-- 'cat':4



  14. Highlighting Matches (ts_headline)

    15. SELECT ts_headline(
    'english',
    'The big car crashed near the big house',
    to_tsquery('big & car')
);



  15. Using Custom Dictionaries

    16. # thesaurus file example:
supercar : car, sports car, racecar



  16. Practical Example: Search Blog Posts

    17. ALTER TABLE blog_posts
ADD COLUMN fts tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('english', title), 'A') ||
        setweight(to_tsvector('english', body),  'D')
    ) STORED;

CREATE INDEX idx_blog_fts ON blog_posts USING gin (fts);

SELECT
    id,
    ts_headline(body, q) AS preview,
    ts_rank(fts, q) AS rank
FROM blog_posts,
     to_tsquery('english', 'postgres & search') q
WHERE fts @@ q
ORDER BY rank DESC;


User-Defined Functions (UDFs) in PostgreSQL

  1. Introduction



  2. Basic SQL Functions

    3. CREATE OR REPLACE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
LANGUAGE sql
AS
$$
    SELECT a + b;
$$;


    SELECT add_numbers(10, 20);   -- 30


  3. Functions Returning NULL, STRICT Functions

    4. CREATE FUNCTION multiply(a INT, b INT)
RETURNS INT
LANGUAGE sql
STRICT
AS
$$
    SELECT a * b;
$$;


    SELECT multiply(NULL, 5);  -- NULL


  4. IMMUTABLE, STABLE, VOLATILE

    5. CategoryDescriptionExamples
    IMMUTABLE Always returns the same output for the same input pure math, string functions
    STABLE Same result within a single query, but may change between queries now(), current_setting()
    VOLATILE Can change anytime, cannot be optimized random(), sequences, updates 

    CREATE FUNCTION greet(name TEXT)
RETURNS TEXT
LANGUAGE sql
IMMUTABLE
AS
$$
    SELECT 'Hello ' || name;
$$;


  5. PL/pgSQL Functions (Procedural Language)

    6. CREATE OR REPLACE FUNCTION factorial(n INT)
RETURNS INT
LANGUAGE plpgsql
AS
$$
DECLARE
    result INT := 1;
BEGIN
    IF n < 0 THEN
        RAISE EXCEPTION 'Negative numbers not allowed';
    END IF;

    FOR i IN 1..n LOOP
        result := result * i;
    END LOOP;

    RETURN result;
END;
$$;


    SELECT factorial(5);  -- 120


  6. Using Variables, BEGIN...END Blocks
    7. CREATE FUNCTION format_user(first TEXT, last TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS
$$
DECLARE
    full TEXT;
BEGIN
    full := initcap(first || ' ' || last);
    RETURN full;
END;
$$;


  7. Control Structures (IF, CASE, LOOP)

    8. CREATE FUNCTION classify(score INT)
RETURNS TEXT
LANGUAGE plpgsql
AS
$$
BEGIN
    IF score >= 90 THEN
        RETURN 'A';
    ELSIF score >= 80 THEN
        RETURN 'B';
    ELSE
        RETURN 'C';
    END IF;
END;
$$;


  8. RETURN QUERY for Set-Returning Functions

    9. CREATE FUNCTION get_users_with_gmail()
RETURNS TABLE(id INT, email TEXT)
LANGUAGE plpgsql
AS
$$
BEGIN
    RETURN QUERY
    SELECT id, email FROM users WHERE email LIKE '%@gmail.com';
END;
$$;


    SELECT * FROM get_users_with_gmail();


  9. Returning Custom Composite Types

    10. CREATE TYPE user_info AS (
    id INT,
    name TEXT,
    created TIMESTAMP
);


    CREATE FUNCTION get_user(uid INT)
RETURNS user_info
LANGUAGE sql
AS
$$
    SELECT id, name, created_at
    FROM users
    WHERE id = uid;
$$;


  10. Functions Returning JSON or JSONB

    11. CREATE FUNCTION product_json(pid INT)
RETURNS JSONB
LANGUAGE sql
AS
$$
    SELECT jsonb_build_object(
        'id', id,
        'name', name,
        'price', price
    )
    FROM products
    WHERE id = pid;
$$;


  11. Security Definer Functions

    12. CREATE FUNCTION read_logs()
RETURNS SETOF logs
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
AS
$$
    SELECT * FROM logs;
$$;


  12. Managing search_path Safely

    13. ALTER FUNCTION read_logs() SET search_path = public;


  13. Error Handling and Exception Blocks

    14. CREATE OR REPLACE FUNCTION safe_div(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS
$$
BEGIN
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RETURN NULL;
END;
$$;


  14. RAISE Statements

    15. RAISE NOTICE 'User %, score %', name, score;
RAISE WARNING 'Low disk space';
RAISE EXCEPTION 'Invalid input: %', value;


  15. Overloading Functions

    16. CREATE FUNCTION info(x INT) RETURNS TEXT AS
$$ SELECT 'int' $$ LANGUAGE sql;
CREATE FUNCTION info(x TEXT) RETURNS TEXT AS
$$ SELECT 'text' $$ LANGUAGE sql;

SELECT info(42);      -- 'int'
SELECT info('foo');   -- 'text'


  16. C-Language Functions (Overview)

    17. #include "postgres.h"
#include "fmgr.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(add_one);

Datum add_one(PG_FUNCTION_ARGS)
{
    int32 arg = PG_GETARG_INT32(0);
    PG_RETURN_INT32(arg + 1);
}


    CREATE FUNCTION add_one(int)
RETURNS int
AS 'myext', 'add_one'
LANGUAGE c STRICT;


  17. Dropping and Replacing Functions

    18. DROP FUNCTION add_numbers(INT, INT);



  18. Listing All User-Defined Functions

    19. SELECT
    proname,
    pg_get_functiondef(p.oid)
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema');


  19. Practical Real-World UDF Examples

    20. -- Normalize email
CREATE FUNCTION clean_email(e TEXT)
RETURNS TEXT
IMMUTABLE
LANGUAGE sql
AS
$$ SELECT lower(trim(e)) $$;

-- Generate URL slug
CREATE FUNCTION slugify(s TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS
$$
BEGIN
    RETURN regexp_replace(lower(s), '[^a-z0-9]+', '-', 'g');
END;
$$;

-- Increase stock safely
CREATE FUNCTION add_stock(pid INT, amount INT)
RETURNS VOID
LANGUAGE plpgsql
AS
$$
BEGIN
    UPDATE products
    SET stock = stock + amount
    WHERE id = pid;
END;
$$;


Stored Procedures

  1. Introduction



  2. Basic Syntax
    3. CREATE PROCEDURE procedure_name(arg1 type1, arg2 type2)
LANGUAGE plpgsql
AS
$$
BEGIN
    -- logic
END;
$$;


    CALL procedure_name(value1, value2);


  3. Core Differences Between PROCEDURE and FUNCTION

    4. FeatureFUNCTIONPROCEDURE
    Returns values Yes (scalar, composite, set) No
    Executed with SELECT or PERFORM CALL
    Transaction control No Yes(COMMIT / ROLLBACK)
    Parallel safety Declarative (IMMUTABLE / STABLE / VOLATILE) Not applicable
    Use cases Reusable expressions, business logic Batch jobs, maintenance tasks, ETL


  4. Simple Stored Procedure Example

    5. CREATE OR REPLACE PROCEDURE log_message(msg TEXT)
LANGUAGE plpgsql
AS
$$
BEGIN
    INSERT INTO logs(message, created_at)
    VALUES (msg, NOW());
END;
$$;

CALL log_message('Hello from procedure!');



  5. Procedures With Transaction Control

    6. CREATE OR REPLACE PROCEDURE batch_insert()
LANGUAGE plpgsql
AS
$$
BEGIN
    INSERT INTO events(data) VALUES ('step 1');
    COMMIT;   -- commit early

    INSERT INTO events(data) VALUES ('step 2');
    ROLLBACK; -- rollback this part

    INSERT INTO events(data) VALUES ('step 3');
END;
$$;

CALL batch_insert();



  6. Using IN, OUT, INOUT Parameters

    7. CREATE PROCEDURE add_and_double(
    IN a INT,
    IN b INT,
    OUT sum_val INT,
    OUT double_sum INT
)
LANGUAGE plpgsql
AS
$$
BEGIN
    sum_val := a + b;
    double_sum := (a + b) * 2;
END;
$$;

CALL add_and_double(3, 5, NULL, NULL);



  7. Using INOUT Parameters

    8. CREATE PROCEDURE increment(INOUT x INT)
LANGUAGE plpgsql
AS
$$
BEGIN
    x := x + 1;
END;
$$;

CALL increment(10);   -- returns 11


  8. Procedures With Dynamic SQL (EXECUTE)

    9. CREATE PROCEDURE drop_table_if_exists(tname TEXT)
LANGUAGE plpgsql
AS
$$
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(tname);
END;
$$;

CALL drop_table_if_exists('old_data');


  9. Procedures and Exception Handling

    10. CREATE PROCEDURE safe_insert(val INT)
LANGUAGE plpgsql
AS
$$
BEGIN
    INSERT INTO numbers(num) VALUES (val);
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'Duplicate ignored: %', val;
END;
$$;

CALL safe_insert(10);


  10. Procedures Calling Other Procedures

    11. CREATE PROCEDURE outer_proc()
LANGUAGE plpgsql
AS
$$
BEGIN
    CALL inner_proc();
END;
$$;



  11. Procedures vs DO Blocks

    12. FeaturePROCEDUREDO Block
    Persistence Stored permanently Temporary (inline execution)
    Transaction control Yes No
    Parameters Yes No
    Used in extensions Yes No


  12. Replacing and Dropping Procedures

    13. DROP PROCEDURE log_message(TEXT);

CREATE OR REPLACE PROCEDURE log_message(TEXT) ...



  13. Listing All Procedures

    14. SELECT
    p.proname,
    pg_get_function_arguments(p.oid) AS args,
    n.nspname AS schema
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.prokind = 'p';     -- 'p' = procedure


  14. Security Considerations

    15. ALTER PROCEDURE log_message(TEXT)
SET search_path = public;



  15. Real-World Example: ETL Batch Job

    16. CREATE OR REPLACE PROCEDURE nightly_etl()
LANGUAGE plpgsql
AS
$$
BEGIN
    -- Step 1: staging load
    INSERT INTO staging SELECT * FROM raw_data;
    COMMIT;

    -- Step 2: transformations
    UPDATE staging SET cleaned = TRUE WHERE data LIKE '%ok%';
    COMMIT;

    -- Step 3: merge into production
    INSERT INTO production SELECT * FROM staging;
    COMMIT;

    -- Step 4: cleanup
    TRUNCATE staging;
END;
$$;

CALL nightly_etl();



Transactions, COMMIT, and ROLLBACK in PostgreSQL

  1. Introduction



  2. Starting a Transaction

    3. BEGIN;
-- or: START TRANSACTION;



  3. COMMIT: Saving All Changes

    4. BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;   -- both updates permanently saved


  4. ROLLBACK: Undoing All Changes

    5. BEGIN;

UPDATE products SET stock = stock - 10 WHERE id = 100;
UPDATE products SET stock = stock + 10 WHERE id = 200;

ROLLBACK;   -- both updates undone



  5. Autocommit Behavior

    6. UPDATE users SET name = 'A';   -- auto-committed immediately



  6. Combining Multiple Operations

    7. BEGIN;

INSERT INTO orders(user_id, total) VALUES (7, 250);
INSERT INTO order_items(order_id, product_id, qty)
VALUES (currval('orders_id_seq'), 42, 3);

-- If anything goes wrong:
-- ROLLBACK;

COMMIT;



  7. Savepoints: Partial Rollback

    8. BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;

SAVEPOINT s1;

UPDATE accounts SET balance = balance + 500 WHERE id = 99; -- account 99 doesn't exist

ROLLBACK TO s1;  -- undo only second update

COMMIT;



  8. Transaction Behavior in Functions

    9. -- INVALID inside functions:
COMMIT;
ROLLBACK;



  9. Transaction Behavior in Procedures

    10. CREATE PROCEDURE demo()
LANGUAGE plpgsql
AS
$$
BEGIN
    INSERT INTO logs(msg) VALUES ('step 1');
    COMMIT;   -- allowed

    INSERT INTO logs(msg) VALUES ('step 2');
    ROLLBACK; -- undo step 2 only

    INSERT INTO logs(msg) VALUES ('step 3'); -- part of new transaction
END;
$$;

CALL demo();


  10. Transaction Isolation Levels

    11. BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- work inside transaction

COMMIT;

    Isolation Level Description (Detailed)
    READ COMMITTED
    • This is the default isolation level in PostgreSQL.
    • Each individual SQL statement sees a snapshot of data committed before that statement began.
    • If another transaction commits in-between your statements, your next statement will see the new data.
    • Prevents dirty reads (you never see uncommitted data).
    • Allows non-repeatable reads:
      The same row queried twice may return different values if another transaction modifies and commits it between your statements.
    • Allows phantom reads:
      Repeated SELECT queries may return more or fewer rows.
    • Snapshot: one snapshot per statement.
    REPEATABLE READ
    • The transaction sees a single consistent snapshot taken when the transaction begins.
    • No matter what other transactions commit during your transaction, your reads stay the same.
    • Prevents:
      • dirty reads: never see uncommitted data
      • non-repeatable reads: reading the same row twice always gives the same result
    • Allows phantom reads:
      A repeated query on ranges may still return additional rows depending on the situation.
    • May allow write skew anomalies (a type of race condition) unless SERIALIZABLE is used.
    • Snapshot: one snapshot for the entire transaction.
    SERIALIZABLE
    • The strongest isolation level — ensures the database behaves as if transactions were executed one by one, sequentially, even though they run concurrently.
    • Automatically prevents:
      • dirty reads
      • non-repeatable reads
      • phantom reads
      • write skew anomalies
    • Requires PostgreSQL to automatically detect dangerous conflicts.
    • If two concurrent transactions could lead to a serialization anomaly, PostgreSQL aborts one with:
    ERROR: could not serialize access due to read/write dependencies
    • Your application must retry the entire transaction.
    • Snapshot: similar to REPEATABLE READ, but with extra checks using predicate locks to avoid anomalies.


  11. What Happens During Errors?

    12. BEGIN;

INSERT INTO a VALUES (1);
INSERT INTO a VALUES (1);  -- duplicate key error, transaction aborted

-- Now ANY statement will error until ROLLBACK is issued

ROLLBACK;   -- reset state


  12. Real-World Example: Bank Transfer

    13. BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;   -- transfer completed



  13. Real-World Example: Repairing Data

    14. BEGIN;

DELETE FROM logs WHERE created_at < NOW() - INTERVAL '90 days';

-- Oops, wrong condition?
ROLLBACK;

-- try again
BEGIN;
DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days';
COMMIT;



Sequences in PostgreSQL

  1. A sequence in PostgreSQL is a special object that generates a sequence of numeric values, usually used for auto-incrementing primary keys.


  2. Sequences are independent database objects and not tied to a table unless you explicitly connect them.


  3. They are non-transactional counters:


  4. PostgreSQL automatically creates sequences for SERIAL, BIGSERIAL, and GENERATED AS IDENTITY columns.


  5. Creating a Sequence

    6. CREATE SEQUENCE user_id_seq;


    CREATE SEQUENCE order_seq
    START WITH 1000
    INCREMENT BY 5
    MINVALUE 1
    MAXVALUE 1000000
    CACHE 50
    CYCLE;



  6. Getting Values From a Sequence

    7. SELECT nextval('user_id_seq');  -- 1
SELECT nextval('user_id_seq');  -- 2
SELECT currval('user_id_seq');  -- 2 (same session only)


    SELECT currval('user_id_seq');
-- ERROR: currval of sequence "user_id_seq" is not yet defined in this session


  7. Manually Setting the Value

    8. SELECT setval('user_id_seq', 500);
SELECT nextval('user_id_seq');  -- 501


    SELECT setval('user_id_seq', 500, false); -- next nextval = 500
SELECT nextval('user_id_seq');            -- 500


  8. Linking a Sequence to a Table Column

    9. CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT
);


    CREATE SEQUENCE users_id_seq
OWNED BY users.id;

ALTER TABLE users ALTER COLUMN id SET DEFAULT nextval('users_id_seq');



  9. IDENTITY Columns (Modern Alternative to SERIAL)

    10. CREATE TABLE orders (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    total NUMERIC
);



  10. Using a Sequence in INSERT Statements

    11. INSERT INTO users(id, name)
VALUES (nextval('user_id_seq'), 'Alice');


    INSERT INTO users(name)
VALUES ('Bob');


  11. Viewing Sequence Properties

    12. SELECT * FROM user_id_seq;
-- or
SELECT * FROM pg_sequences WHERE schemaname = 'public';


    SELECT pg_get_serial_sequence('users', 'id');


    SELECT * FROM pg_class WHERE relkind = 'S';


  12. Concurrency & Non-Transactional Behavior

    13. BEGIN;
SELECT nextval('user_id_seq');  -- 10
ROLLBACK;

SELECT nextval('user_id_seq');  -- 11 (NOT 10)


  13. Sequences and Race Conditions

    14. -- Transaction A and B both run:
SELECT nextval('user_id_seq');

-- A gets 12
-- B gets 13


  14. Resetting or Restarting a Sequence

    15. ALTER SEQUENCE user_id_seq RESTART WITH 1;


    SELECT setval('user_id_seq', 1, false);



  15. Dropping a Sequence

    16. DROP SEQUENCE user_id_seq;



  16. Real-World Examples

    17. -- Create a custom global invoice numbering generator
CREATE SEQUENCE invoice_seq START WITH 20240001;

INSERT INTO invoices(id, total)
VALUES (nextval('invoice_seq'), 99.99);


    -- Use sequence to shard IDs by region
CREATE SEQUENCE region1_seq START 1 INCREMENT 10;
CREATE SEQUENCE region2_seq START 2 INCREMENT 10;


    -- Regenerating sequence after manual data load
SELECT setval('user_id_seq', (SELECT MAX(id) FROM users));



Indexes in PostgreSQL

  1. Introduction



  2. Basic Index Creation

    3. CREATE INDEX idx_users_email
ON users(email);



  3. The Main Index Types

    4. Index TypeBest ForNotes
    B-tree Equality (=) and range (<, >) queries Default index, supports sorting and UNIQUE constraints
    Hash Equality lookups only Rarely used, B-tree is usually faster
    GIN Arrays, JSONB, full-text search Multivalued indexing, slower to update but very fast lookup
    GiST Geometric types, full-text search, ranges Flexible balanced tree framework
    SP-GiST Partitioned search spaces: IP ranges, quadtrees Specialized high-performance search structures
    BRIN Very large tables with natural ordering (timestamps, IDs) Tiny index size, block ranges, extremely fast to build
    Bloom extension Many-column indexes Probabilistic index; extension: pg_bloom


  4. B-Tree Indexes (Default)

    5. WHERE email = 'a@example.com'
WHERE age > 18
WHERE price BETWEEN 100 AND 200
ORDER BY created_at DESC


    CREATE UNIQUE INDEX idx_users_email_unique ON users(email);


  5. Expression Indexes (Function Indexes)

    6. CREATE INDEX idx_users_lower_email
ON users(LOWER(email));


    SELECT * FROM users WHERE LOWER(email) = 'a@example.com';


  6. Partial Indexes (Filtered Indexes)

    7. CREATE INDEX idx_active_users
ON users(email)
WHERE active = true;



  7. Multi-Column Indexes

    8. CREATE INDEX idx_users_last_first
ON users(last_name, first_name);



  8. Index-Only Scans

    9. CREATE INDEX idx_users_email_only
ON users(email);

SELECT email FROM users WHERE email LIKE '%@gmail.com';



  9. GIN Indexes

    10. CREATE INDEX idx_tags_gin
ON posts USING gin(tags);


    SELECT * FROM posts WHERE tags && '{sql}';
SELECT * FROM posts WHERE tags @> '{programming}';
SELECT * FROM data WHERE jsonb_data @> '{"user": {"id": 5}}';


  10. GiST Indexes

    11. CREATE INDEX idx_ranges_gist
ON events USING gist(period);


  11. SP-GiST Indexes

    12. CREATE INDEX idx_ip_spgist
ON ips USING spgist(ip_address);


  12. BRIN Indexes

    13. CREATE INDEX idx_logs_brin
ON logs USING brin(timestamp);


  13. Index Bloat

    14. REINDEX INDEX idx_users_email;
REINDEX TABLE users;


  14. Fillfactor

    15. CREATE INDEX idx_items_price
ON items(price)
WITH (fillfactor = 70);


  15. NULL Handling in Indexes

    16. CREATE INDEX idx_not_null_price
ON items(price)
WHERE price IS NOT NULL;


  16. Collation-Aware Indexing

    17. CREATE INDEX idx_names_collation
ON people(name COLLATE "de_DE");


  17. Operator Classes

    18. CREATE INDEX idx_users_email_pattern
ON users(email text_pattern_ops);


  18. Covering Indexes (INCLUDE)

    19. CREATE INDEX idx_orders_user_date
ON orders(user_id)
INCLUDE (order_date);


  19. Dropping Indexes

    20. DROP INDEX idx_users_email;


  20. Real-World Index Examples

    21. -- Search by email, case-insensitive
CREATE INDEX idx_users_lower_email
ON users(LOWER(email));

-- Fast soft-deletes
CREATE INDEX idx_posts_not_deleted
ON posts(id)
WHERE deleted_at IS NULL;

-- Fast JSONB lookup
CREATE INDEX idx_events_jsonb
ON events USING gin(data);

-- Speed up sorting queries
CREATE INDEX idx_logs_ts_desc
ON logs(timestamp DESC);

-- Range queries on large time-based table
CREATE INDEX idx_sensor_brin
ON sensor_data USING brin(recorded_at);


Composite Types

  1. Introduction



  2. Creating a Composite Type

    3. CREATE TYPE address AS (
    street      TEXT,
    city        TEXT,
    postal_code TEXT,
    country     TEXT
);



  3. Using Composite Types as Table Columns

    4. CREATE TABLE users (
    id      SERIAL PRIMARY KEY,
    name    TEXT,
    addr    address
);


    INSERT INTO users(name, addr)
VALUES (
    'Alice',
    ROW('Main Street 1', 'Berlin', '10115', 'Germany')
);


    INSERT INTO users(name, addr)
VALUES (
    'Bob',
    ('Highway 9', 'Munich', '80331', 'Germany')::address
);


  4. Accessing Fields of a Composite Column

    5. SELECT
    name,
    addr.city,
    addr.country
FROM users
WHERE addr.postal_code = '10115';


    CREATE INDEX idx_users_addr_city
ON users((addr).city);


  5. Row Constructor: ROW(...)

    6. SELECT ROW(1, 'abc', now());


    SELECT ROW(1, 2) < ROW(1, 3);                -- true
SELECT ROW('Alice', 20) = ROW('Alice', 20);  -- true


  6. Composite Types in Functions

    7. CREATE TYPE user_summary AS (
    id     INT,
    name   TEXT,
    city   TEXT
);


    CREATE FUNCTION get_user_summary(uid INT)
RETURNS user_summary
LANGUAGE sql
AS
$$
    SELECT id, name, (addr).city
    FROM users
    WHERE id = uid;
$$;


    SELECT * FROM get_user_summary(1);


  7. Returning Sets of Composite Types

    8. CREATE FUNCTION get_all_summaries()
RETURNS SETOF user_summary
LANGUAGE sql
AS
$$
    SELECT id, name, (addr).city
    FROM users;
$$;


    SELECT * FROM get_all_summaries();


  8. Composite Types in PL/pgSQL Variables

    9. DECLARE
    u users%ROWTYPE;
    a address;
BEGIN
    SELECT addr INTO a FROM users WHERE id = 1;

    u.id := 1;
    u.name := 'Test';
    u.addr := a;
END;



  9. Modifying Specific Fields of Composite Columns

    10. UPDATE users
SET addr = addr::address  -- explicit cast recommended
        || ROW(NULL, 'Hamburg', NULL, NULL)
WHERE id = 1;


    UPDATE users
SET addr = (addr).city := 'Hamburg';
-- Invalid syntax, but commonly attempted


    UPDATE users
SET addr = (addr).street,
          (addr).city := 'Hamburg',
          (addr).postal_code,
          (addr).country;
-- must rebuild full composite value



  10. Composite Type Literals

    11. SELECT '(Main, Berlin, 10115, Germany)'::address;


    '("123 Ave",("Berlin","DE"))'


  11. Arrays of Composite Types

    12. CREATE TABLE shops (
    id     SERIAL PRIMARY KEY,
    name   TEXT,
    locations address[]
);


    INSERT INTO shops(name, locations)
VALUES (
    'Superstore',
    ARRAY[
        ('Main St', 'Berlin', '10115', 'Germany')::address,
        ('Highway Rd', 'Munich', '80331', 'Germany')::address
    ]
);


  12. Composite Types in JSON Conversion

    13. SELECT to_json(addr) FROM users;


    {
  "street": "Main Street 1",
  "city": "Berlin",
  "postal_code": "10115",
  "country": "Germany"
}


  13. Altering Composite Types

    14. ALTER TYPE address ADD ATTRIBUTE province TEXT;
ALTER TYPE address DROP ATTRIBUTE postal_code;
ALTER TYPE address RENAME ATTRIBUTE street TO street_name;



  14. Dropping Composite Types

    15. DROP TYPE address;



  15. Real-World Examples

    16. -- Geographic point stored as composite
CREATE TYPE geo_point AS (
    lat  DOUBLE PRECISION,
    lon  DOUBLE PRECISION
);

CREATE TABLE events (
    id      SERIAL PRIMARY KEY,
    title   TEXT,
    pos     geo_point
);

INSERT INTO events(title, pos)
VALUES ('Concert', ROW(52.5200, 13.4050));


    -- Composite type as function OUT parameters
CREATE TYPE product_summary AS (
    product_id INT,
    name       TEXT,
    price      NUMERIC
);

CREATE FUNCTION summarize_product(pid INT)
RETURNS product_summary
LANGUAGE sql
AS
$$
    SELECT id, name, price FROM products WHERE id = pid;
$$;


    -- Composite type arrays for time-series sensors
CREATE TYPE measurement AS (
    ts   TIMESTAMP,
    temp NUMERIC,
    hum  NUMERIC
);

CREATE TABLE devices (
    id      SERIAL PRIMARY KEY,
    logs    measurement[]
);


Arrays

  1. PostgreSQL supports true multi-dimensional array types, allowing any base type (integer, text, composite types, enums, domains, etc.) to be represented as an array.


  2. Arrays can be:


  3. Declaring Array Columns

    4. CREATE TABLE products (
    id       SERIAL PRIMARY KEY,
    name     TEXT,
    tags     TEXT[],
    ratings  INT[],
    matrix   INT[][]   -- multi-dimensional
);



  4. Array Literals

    5. INSERT INTO products(name, tags, ratings)
VALUES ('Keyboard', '{mechanical,usb}', '{5,4,5}');


    '{ "a value", "another value" }'


    '{ "a\\tb", "with \\"quotes\\"" }'


  5. Explicit Casting to Arrays

    6. SELECT ARRAY[1, 2, 3]::INT[];
SELECT ARRAY['a', 'b', 'c']::TEXT[];


    SELECT ARRAY[[1,2],[3,4]]::INT[][];


  6. Accessing Array Elements

    7. SELECT
    tags[1],
    ratings[3]
FROM products
WHERE id = 1;


    SELECT ratings[2:4] FROM products;
SELECT matrix[1:2][1] FROM products;


    UPDATE products
SET ratings[2] = 3
WHERE id = 1;


  7. Array Operators

    8. OperatorDescriptionExample
    && Array overlap '{a,b}' && '{b,c}'
    @> Array contains '{a,b,c}' @> '{b}'
    <@ Contained by '{b}' <@ '{a,b,c}'
    || Concatenate '{a,b}' || '{c,d}'
    =, <> Equality '{1,2}' = '{1,2}' 

    SELECT '{a,b}' && '{x,a}';        -- true
SELECT '{a,b,c}' @> '{a,c}';      -- true
SELECT '{a}' <@ '{a,b,c}';        -- true
SELECT '{1,2}' || '{3,4}';        -- {1,2,3,4}


  8. Array Functions

    9. FunctionDescriptionExample
    array_length(arr, dim) Length of dimension array_length('{1,2,3}', 1)
    array_append(arr, elem) Append element array_append('{1,2}', 3)
    array_prepend(elem, arr) Prepend element array_prepend(0, '{1,2}')
    array_cat(a1, a2) Concatenate array_cat('{1,2}', '{3,4}')
    unnest(arr) Expand array to rows SELECT unnest('{1,2,3}')
    string_to_array Parse string to array string_to_array('a,b', ',')
    array_to_string Array to delimited text array_to_string('{a,b}', ',')


  9. Searching Array Columns Efficiently

    10. CREATE INDEX idx_products_tags_gin
ON products USING gin(tags);


    SELECT *
FROM products
WHERE tags @> '{mechanical}';

SELECT *
FROM products
WHERE tags && '{usb,wireless}';


  10. Comparing Arrays

    11. SELECT '{1,2}' = '{1,2}';       -- true
SELECT '{1,2}' <> '{2,1}';      -- true (order matters)



  11. Multi-Dimensional Arrays

    12. SELECT ARRAY[
    [1,2,3],
    [4,5,6],
    [7,8,9]
] AS matrix;


    SELECT matrix[2][3] FROM table_name;   -- element access


    -- INVALID:
SELECT ARRAY[[1,2], [3]];  -- mismatched dimensions


  12. Array Unnesting & Working With Rows

    13. CREATE TABLE products (
    id     INT,
    name   TEXT,
    tags   TEXT[],
    matrix INT[][]
);

INSERT INTO products VALUES
(1, 'Keyboard', '{mechanical,usb,black}', '{{1,2},{3,4}}'),
(2, 'Mouse',    '{wireless,black}',       '{{5,6},{7,8}}');


  13. Unnesting a 1-D Array

    14. SELECT
    id,
    unnest(tags) AS tag
FROM products;

    idtag
    1mechanical
    1usb
    1black
    2wireless
    2black


  14. Unnesting a Multi-Dimensional Array

    15. SELECT
    id,
    unnest(matrix) AS value
FROM products;

    idvalue
    11
    12
    13
    14
    25
    26
    27
    28


  15. Unnesting Multiple Arrays in Parallel

    16. SELECT
    id,
    x,
    y
FROM products,
     unnest(tags, array[1,2,3]) AS t(x, y)
WHERE id = 1;

    idx (tag)y (mapped value)
    1mechanical1
    1usb2
    1black3


  16. Using Unnest With Ordinality

    17. SELECT
    id,
    tag,
    ord
FROM products,
     unnest(tags) WITH ORDINALITY AS t(tag, ord);

    idtagord
    1mechanical1
    1usb2
    1black3
    2wireless1
    2black2


  17. Unnesting and Joining Back to the Table

    18. SELECT p.id, p.name, t.tag
FROM products p
JOIN LATERAL unnest(p.tags) AS t(tag)
ON true;



  18. Unnesting Arrays of Composite Types

    19. CREATE TYPE measurement AS (
    ts   TIMESTAMP,
    val  NUMERIC
);

CREATE TABLE devices (
    id   INT,
    logs measurement[]
);

INSERT INTO devices VALUES
(1, ARRAY[
    ('2024-01-01 10:00', 20.5)::measurement,
    ('2024-01-01 11:00', 21.1)::measurement
]);


    SELECT
    id,
    (log).ts,
    (log).val
FROM devices,
     unnest(logs) AS t(log);

    idtsval
    12024-01-01 10:0020.5
    12024-01-01 11:0021.1


  19. Arrays of Composite Types

    20. CREATE TYPE measurement AS (
    ts   TIMESTAMP,
    temp NUMERIC,
    hum  NUMERIC
);

CREATE TABLE device_logs (
    id      SERIAL PRIMARY KEY,
    logs    measurement[]
);


    INSERT INTO device_logs(logs)
VALUES (ARRAY[
    ('2024-01-01', 20.5, 30)::measurement,
    ('2024-01-01', 21.0, 32)::measurement
]);


  20. Updating Entire Arrays

    21. UPDATE products
SET tags = array_append(tags, 'newtag')
WHERE id = 1;


    UPDATE products
SET ratings = ARRAY[5,5,5];


  21. Deleting Elements From Arrays

    22. UPDATE products
SET tags = array_remove(tags, 'usb')
WHERE id = 1;


    UPDATE products
SET ratings = array_replace(ratings, 1, NULL);


  22. Checking If an Element Exists

    23. SELECT * FROM products
WHERE 'mechanical' = ANY(tags);


    SELECT * FROM products
WHERE 5 = ALL(ratings);  -- all values must equal 5


  23. Dropping an Array Column

    24. ALTER TABLE products DROP COLUMN tags;


  24. Real-World Examples

    25. -- Tagging system
CREATE INDEX idx_posts_tags_gin
ON posts USING gin(tags);

SELECT * FROM posts WHERE tags @> '{sql}';


    -- Storing daily weather measurements
CREATE TABLE weather (
    id    SERIAL,
    temps NUMERIC[]
);

INSERT INTO weather(temps)
VALUES (ARRAY[15.5,16.0,17.2]);


    -- Composite array for sensor logs
CREATE TYPE logentry AS (ts TIMESTAMP, val NUMERIC);

CREATE TABLE sensors (
    id     SERIAL,
    logs   logentry[]
);


ENUM Types in PostgreSQL

  1. Introduction



  2. Creating an ENUM Type

    3. CREATE TYPE order_status AS ENUM (
    'pending',
    'processing',
    'shipped',
    'delivered',
    'cancelled'
);



  3. Using ENUM Types in Tables

    4. CREATE TABLE orders (
    id      SERIAL PRIMARY KEY,
    product TEXT,
    status  order_status NOT NULL
);


    INSERT INTO orders(product, status)
VALUES ('Laptop', 'pending');


    INSERT INTO orders(product, status)
VALUES ('Laptop', 'waiting');   -- ERROR: invalid input value for enum


  4. Ordering and Comparison

    5. SELECT 'pending' < 'processing';     -- true
SELECT 'delivered' > 'shipped';      -- true


    SELECT * FROM orders
ORDER BY status;



  5. Changing ENUM Types

    6. ALTER TYPE order_status ADD VALUE 'returned';


    ALTER TYPE order_status ADD VALUE 'awaiting_payment'
BEFORE 'pending';

ALTER TYPE order_status ADD VALUE 'archived'
AFTER 'cancelled';


  6. Renaming an ENUM Value

    7. ALTER TYPE order_status RENAME VALUE 'pending' TO 'awaiting';



  7. Dropping an ENUM Type

    8. DROP TYPE order_status;


    DROP TYPE order_status CASCADE;


  8. ENUM Arrays

    9. CREATE TABLE tickets (
    id       SERIAL PRIMARY KEY,
    labels   order_status[]
);


    INSERT INTO tickets(labels)
VALUES ('{pending,shipped}');


    SELECT 'shipped' = ANY(labels) FROM tickets;


  9. Using ENUMs in Composite Types

    10. CREATE TYPE product_info AS (
    name   TEXT,
    status order_status
);


    SELECT (ROW('Tablet','pending')::product_info).status;


  10. ENUM Type Casting and Implicit Behavior

    11. SELECT 'pending'::order_status;


    SELECT status = 'pending' FROM orders;    -- works (implicit)


    SELECT ARRAY['pending','shipped'];            -- ERROR
SELECT ARRAY['pending'::order_status,'shipped'::order_status];


  11. ENUM vs CHECK Constraints

    12. ApproachProsCons
    ENUM Type
    • Faster comparisons
    • Compact storage
    • Ordered
    • Strong type enforcement
    • Hard to remove/rename values
    • Schema migration overhead
    CHECK constraint
    • Easier to modify
    • No global type coupling
    • More storage
    • No meaningful ordering
    • Duplicate constraints across tables


  12. Performance Considerations

    13. CREATE INDEX idx_orders_status ON orders(status);


  13. Migrating ENUM Values Safely

    14. ALTER TYPE order_status ADD VALUE 'in_transit';



    -- 1. Rename old enum
ALTER TYPE order_status RENAME TO order_status_old;

-- 2. Create new enum with correct values
CREATE TYPE order_status AS ENUM ('pending','processing','...');

-- 3. Convert columns
ALTER TABLE orders
ALTER COLUMN status TYPE order_status
USING status::TEXT::order_status;

-- 4. Drop old enum
DROP TYPE order_status_old;


  14. Real-World Examples

    15. -- User roles
CREATE TYPE user_role AS ENUM ('user','moderator','admin');

CREATE TABLE users (
    id    SERIAL,
    name  TEXT,
    role  user_role NOT NULL DEFAULT 'user'
);


    -- Ticket priorities with ordering
CREATE TYPE priority AS ENUM ('low','medium','high','urgent');

SELECT * FROM tickets ORDER BY priority;   -- correct severity order


    -- Payment status usage
CREATE TYPE payment_status AS ENUM ('created','paid','refunded');

CREATE TABLE invoices (
    id      SERIAL,
    amount  NUMERIC,
    status  payment_status
);


The xml Type in PostgreSQL

  1. PostgreSQL provides a native xml data type that stores well-formed XML documents or fragments.


  2. It supports:


  3. The xml type stores the document in text form but performs structure validation according to XML standards.


  4. Declaring XML Columns

    5. CREATE TABLE books (
    id      SERIAL PRIMARY KEY,
    title   TEXT,
    metadata XML
);


    INSERT INTO books(title, metadata)
VALUES (
    'Dune',
    '<book><author>Herbert</author><year>1965</year></book>'
);


    INSERT INTO books(metadata)
VALUES ('<bad xml>');   -- ERROR: not well-formed XML


  5. XML Parsing and Validation



  6. Constructing XML with XMLPARSE

    7. SELECT XMLPARSE(DOCUMENT '<root><a>1</a></root>');


    SELECT XMLPARSE(CONTENT '<a>value</a>');



  7. Converting TEXT to XML and XML to TEXT

    8. SELECT '<tag>hello</tag>'::xml;
SELECT xmlserialize(CONTENT metadata AS TEXT) FROM books;



  8. Extracting Data with XPath

    9. SELECT xpath('/book/author/text()', metadata)
FROM books;


    SELECT (xpath('/book/year/text()', metadata))[1]::text
FROM books;


  9. Querying XML with Namespaces

    10. SELECT xpath(
    'declare default element namespace "http://example.com/book";
     /book/author/text()',
    metadata
)
FROM books;


  10. Constructing XML with xmlelement, xmlforest, xmlconcat

    11. SELECT xmlelement(
    NAME book,
    xmlelement(NAME title, 'Dune'),
    xmlelement(NAME year, 1965)
);


    SELECT xmlforest(
    title AS title,
    'Frank Herbert' AS author
);


    SELECT xmlconcat(
    '<a>1</a>'::xml,
    '<b>2</b>'::xml
);


  11. Using XMLTABLE (SQL Standard)

    12. SELECT *
FROM XMLTABLE(
    '/book'
    PASSING '<book><title>Dune</title><year>1965</year></book>'::xml
    COLUMNS
        title TEXT PATH 'title',
        year  INT  PATH 'year'
);

    titleyear
    Dune1965


  12. XML Schema Validation (XSD)

    13. SELECT xml_is_well_formed_document(metadata);
SELECT xmlvalidate(metadata, '...xsd contents...'::xml);



  13. Indexing XML Data

    14. CREATE INDEX idx_books_author
ON books ((xpath('/book/author/text()', metadata))[1]);


    CREATE INDEX idx_books_jsonb
ON books USING gin (xml_to_jsonb(metadata));


  14. XML Escaping Rules

    15. CharacterEscape
    <&lt;
    >&gt;
    &&amp;
    "&quot;
    '&apos;

    SELECT '<tag value="hello"/>'::xml;


  15. XML vs JSON

    16. FeatureXMLJSONB
    Data Model Tree with attributes, namespaces, mixed content Key-value, arrays, no attributes
    Schema DTD/XSD support JSON Schema (external)
    Indexing Expression-only Native GIN indexing
    Use Cases Interoperability, legacy systems, SOAP APIs, logs, analytics


  16. Real-World Examples

    17. -- Book metadata
INSERT INTO books(title, metadata) VALUES (
    'Hyperion',
    '<book><author>Simmons</author><year>1989</year></book>'
);


    -- Extracting author names
SELECT title,
       (xpath('/book/author/text()', metadata))[1]::text AS author
FROM books;


    -- Converting a relational row to XML
SELECT xmlelement(
    NAME book,
    xmlforest(title, metadata)
)
FROM books;


    -- Using XMLTABLE for report generation
SELECT b.title, x.year
FROM books b,
     XMLTABLE(
         '/book'
         PASSING b.metadata
         COLUMNS year INT PATH 'year'
     ) AS x;


JSON and JSONB Types in PostgreSQL

  1. PostgreSQL provides two native JSON types: json and jsonb.


  2. Both can store arbitrary structured JSON documents:


  3. The key difference is:


  4. Modern PostgreSQL applications use jsonb almost exclusively.


  5. json vs jsonb

    6. Featurejsonjsonb
    Storage Exact text Binary parsed structure
    Duplicate Keys Preserved Last key wins
    Whitespace Preserved Discarded
    Indexing No GIN/GiST indexes supported
    Modification Slower Faster operations and path queries
    Sort Order N/A Stable order



  6. Creating JSON Columns

    7. CREATE TABLE users (
    id      SERIAL PRIMARY KEY,
    profile JSONB
);


    INSERT INTO users(profile)
VALUES ('{"name": "Alice", "age": 30, "skills": ["sql","python"]}');


  7. Accessing JSON Fields

    8. OperatorDescription
    -> Get JSON value (object or array) by key or index (returns JSON)
    ->> Get value as text
    #> Get JSON from a path array
    #>> Get text from a path array 

    SELECT
    profile->'name' AS name_json,
    profile->>'name' AS name_text,
    profile->'skills'->0 AS first_skill
FROM users;


    -- Using path arrays
SELECT profile#>'{address, city}' FROM users;
SELECT profile#>>'{address, zip}' FROM users;


  8. Updating JSONB Fields

    9. -- Replace a key
UPDATE users
SET profile = jsonb_set(profile, '{age}', '31'::jsonb)
WHERE id = 1;


    -- Add new key
UPDATE users
SET profile = jsonb_set(profile, '{email}', '"a@example.com"', true);


    -- Update nested arrays
UPDATE users
SET profile = jsonb_set(profile, '{skills,1}', '"postgresql"')
WHERE id = 1;


  9. Deleting Fields

    10. UPDATE users
SET profile = profile - 'age';


    UPDATE users
SET profile = profile #- '{skills,0}';


  10. JSONB Containment Operators

    11. OperatorMeaningExample
    @> Contains '{"a":1,"b":2}' @> '{"a":1}'
    <@ Contained by '[1,2]' <@ '[1,2,3]'
    ? Key exists '{"a":1}' ? 'a'
    ?| Any key exists '{"a":1,"b":2}' ?| '{a,c}'
    ?& All keys exist '{"a":1,"b":2}' ?& '{a,b}' 

    SELECT * FROM users
WHERE profile @> '{"skills": ["sql"]}';



  11. Querying Inside JSON Arrays

    12. SELECT * FROM users
WHERE profile->'skills' ? 'python';


    SELECT * FROM users
WHERE 'sql' = ANY(jsonb_array_elements_text(profile->'skills'));


  12. Indexing JSONB

    13. CREATE INDEX idx_users_profile_gin
ON users USING gin(profile);



    SELECT * FROM users
WHERE profile @> '{"age": 30}';


  13. Expression Indexes on JSON Fields

    14. CREATE INDEX idx_users_city
ON users ((profile#>>'{address,city}'));


    SELECT *
FROM users
WHERE profile#>>'{address,city}' = 'Berlin';


  14. Converting Between JSON, JSONB, and TEXT

    15. SELECT '{"a":1}'::jsonb;
SELECT profile::json;
SELECT profile::text;


    SELECT to_json(ARRAY[1,2,3]);
SELECT jsonb_pretty(profile) FROM users;


  15. Aggregating to JSON

    16. SELECT json_agg(profile) FROM users;


    SELECT jsonb_build_object('count', COUNT(*), 'users', jsonb_agg(id))
FROM users;


  16. Building JSON Documents

    17. SELECT jsonb_build_object(
    'name', name,
    'skills', skills,
    'age', age
)
FROM employees;


  17. JSONB Path Queries (@ SQL/JSON)

    18. SELECT jsonb_path_query(profile, '$.skills[*] ? (@ == "sql")')
FROM users;


    SELECT * FROM users
WHERE jsonb_path_exists(profile, '$.age ? (@ > 20)');


  18. Performance Notes

    19. CREATE INDEX idx_users_profile_pathops
ON users USING gin(profile jsonb_path_ops);


  19. Real-World Examples

    20. -- Find users in a specific city
SELECT * FROM users
WHERE profile#>>'{address,city}' = 'Berlin';


    -- Check if user has a skill
SELECT * FROM users
WHERE profile->'skills' ? 'postgresql';


    -- Merge JSON objects
SELECT profile || '{"active":true}'::jsonb
FROM users;


    -- Build a complete API response
SELECT jsonb_build_object(
    'status', 'ok',
    'timestamp', now(),
    'data', jsonb_agg(profile)
)
FROM users;


GIN Indexes in PostgreSQL

  1. GIN stands for Generalized Inverted Index.


  2. GIN indexes are designed for indexing values that contain multiple components, such as:


  3. GIN is optimized for containment queries:


  4. GIN indexes are usually much larger than B-tree, but provide excellent query performance on complex data.


  5. How GIN Works Internally



  6. Creating a GIN Index

    7. CREATE INDEX idx_users_tags_gin
ON users USING gin(tags);



  7. GIN Indexes on Arrays

    8. CREATE TABLE products (
    id   SERIAL,
    tags TEXT[]
);

CREATE INDEX idx_products_tags_gin
ON products USING gin(tags);


    SELECT * FROM products WHERE tags @> '{sql}';
SELECT * FROM products WHERE tags && '{postgres,sql}';



  8. GIN Indexes on JSONB

    9. CREATE TABLE events (
    id   SERIAL,
    data JSONB
);

CREATE INDEX idx_events_data_gin
ON events USING gin(data);


    SELECT * FROM events WHERE data @> '{"type":"login"}';
SELECT * FROM events WHERE data ? 'user_id';
SELECT * FROM events WHERE data @> '{"tags":["security"]}';



  9. jsonb_path_ops vs default jsonb_ops

    10. CREATE INDEX idx_events_data_pathops
ON events USING gin(data jsonb_path_ops);


  10. GIN for Full-text Search (TSVECTOR)

    11. CREATE TABLE docs (
    id    SERIAL,
    body  TEXT
);

CREATE INDEX idx_docs_tsv
ON docs USING gin(to_tsvector('english', body));


    SELECT * FROM docs
WHERE to_tsvector('english', body) @@ to_tsquery('security & data');



  11. GIN for hstore

    12. CREATE EXTENSION hstore;

CREATE TABLE settings (
    id    SERIAL,
    attrs hstore
);

CREATE INDEX idx_settings_attrs
ON settings USING gin(attrs);


    SELECT * FROM settings
WHERE attrs ? 'theme';


  12. GIN with pg_trgm (trigram search)

    13. CREATE EXTENSION pg_trgm;

CREATE INDEX idx_users_name_trgm
ON users USING gin (name gin_trgm_ops);



  13. GIN Index Options

    14. CREATE INDEX idx_logs_data_gin
ON logs USING gin(data)
WITH (fastupdate = on);



  14. Maintenance & Reindexing

    15. VACUUM (ANALYZE) table_name;
REINDEX INDEX idx_name;



  15. Performance Notes



  16. Real-World Examples

    17. -- Search logs by JSONB keys
SELECT * FROM logs
WHERE data @> '{"status":"error"}';


    -- Find users with certain tags
SELECT * FROM users
WHERE tags && '{music,sql}';


    -- Fast full-text search with trigram indexing
SELECT * FROM documents
WHERE body ILIKE '%database%';


    -- Multi-condition JSONB search
SELECT * FROM events
WHERE data @> '{"user":{"id":5}}';


The search_path in PostgreSQL

  1. Introduction

    2. SHOW search_path;
-- "$user", public



  2. Why Search Path Matters



  3. Setting the Search Path

    4. SET search_path TO myschema, public;


    SET LOCAL search_path TO analytics;
-- Only for duration of current transaction


    ALTER DATABASE mydb SET search_path = myschema, public;
ALTER USER myuser SET search_path = app, public;



  4. How Object Resolution Works

    5. SET search_path TO sales, public;

SELECT customers.id FROM customers;




    SELECT sales.customers.id  -- always resolved exactly
FROM sales.customers;



  5. Search Path and Function Resolution

    6. SET search_path TO analytics, public;

SELECT format_date('2024-01-01');



    SELECT utils.format_date('2024-01-01');


  6. Search Path and Security

    7. SET search_path TO public, malicious_schema;

-- A malicious user creates a function matching your app's name
CREATE FUNCTION malicious_schema.now() RETURNS timestamp AS
$$
    SELECT '1970-01-01'::timestamp;
$$ LANGUAGE sql;




    ALTER ROLE myapp SET search_path = pg_catalog, app, public;


  7. Temporary Tables and Search Path

    8. CREATE TEMP TABLE t(x int);
SHOW search_path;
-- pg_temp, pg_catalog, public



  8. search_path for Extensions

    9. CREATE EXTENSION postgis SCHEMA gis;



  9. Best Practices

    10. CREATE TABLE app.users (...);
CREATE FUNCTION app.calculate() ...;



  10. Real-World Examples

    11. -- Application search path
ALTER ROLE myapp SET search_path = pg_catalog, app, public;


    -- Safely executing migrations
SET search_path = app;
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;


    -- Using multiple schemas for versioning
SET search_path = v2, v1, public;
SELECT get_customer(5);


Casting & Type Conversion in PostgreSQL

  1. Introduction



  2. Explicit Casts

    3. SELECT '123'::INT;
SELECT CAST('123' AS INT);
SELECT CAST(5.8 AS NUMERIC(3,1));


    SELECT 'abc'::INT;
-- ERROR: invalid input syntax for type integer: "abc"


  3. The :: Cast Operator

    4. SELECT now()::DATE;
SELECT 42::TEXT;
SELECT '{1,2,3}'::INT[];


  4. CAST(expr AS type)

    5. SELECT CAST(42 AS TEXT);



  5. Implicit Casts

    6. SELECT 10 + '20';
-- '20' is implicitly cast to INT



  6. Cast Precedence & Promotion Rules

    7. SELECT 1 + 2.5;
-- 1 is promoted to NUMERIC


    SELECT 1.0 + 2;
-- 2 becomes NUMERIC

    SourcePromoted To
    smallintinteger
    integerbigint
    bigintnumeric
    realdouble precision



  7. Casting JSON & JSONB

    8. SELECT '{"a":1}'::json;
SELECT '{"a":1}'::jsonb;


    SELECT ('{"n":42}'::jsonb)->>'n'::INT;


    SELECT (profile->>'age')::INT;


  8. Casting Arrays

    9. SELECT ARRAY['1','2','3']::INT[];


    SELECT '{1,2,3}'::INT[];


    SELECT ARRAY[1,2,3]::TEXT[];


  9. Casting Between Enums and Text

    10. CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

SELECT 'happy'::mood;
SELECT 'happy'::mood::TEXT;



  10. Casting Composite Types

    11. CREATE TYPE point2d AS (x INT, y INT);

SELECT '(3,4)'::point2d;
SELECT ROW(5,6)::point2d;



  11. Domain Casts

    12. CREATE DOMAIN positive_int INT CHECK (VALUE > 0);

SELECT 5::positive_int;
SELECT (-1)::positive_int; -- fails check constraint



  12. Function-based Casts

    13. CREATE FUNCTION text_to_int_safe(text)
RETURNS int
LANGUAGE plpgsql AS
$$
BEGIN
    RETURN CASE WHEN $1 ~ '^\d+$' THEN $1::int ELSE NULL END;
END;
$$;

CREATE CAST (text AS int)
WITH FUNCTION text_to_int_safe(text)
AS ASSIGNMENT;



  13. Create Your Own Type Casts

    14. CREATE CAST (point2d AS text)
WITH FUNCTION point2d_to_text(point2d);



  14. Safe Casting vs Unsafe Casting

    15. SELECT '2024-01-01'::DATE; -- safe
SELECT 'not-a-date'::DATE; -- error



    SELECT '1; DROP TABLE users;'::INT;
-- ERROR (safe)



  15. Type Casting in Expressions

    16. SELECT (1 + '2')::INT;  -- implicit then explicit
SELECT (1 + 2.8)::NUMERIC(4,2);



  16. Real-World Examples

    17. -- Read JSON number as integer
SELECT (data->>'count')::INT FROM metrics;


    -- Convert text array to integer array
SELECT string_to_array('1,2,3', ',')::INT[];


    -- Cast timestamp to date
SELECT created_at::DATE FROM orders;


    -- Cast enum to text
SELECT status::TEXT FROM users;


    -- Format a composite type
SELECT (ROW(5,6)::point2d).x;


Triggers in PostgreSQL

  1. Introduction



  2. Types of Triggers

    3. TypeDescription
    Row-Level Trigger Executed once per affected row. Has access to OLD and NEW records.
    Statement-Level Trigger Executed once per SQL statement, regardless of how many rows are modified.
    BEFORE Trigger Can validate or modify NEW data before writing to the table.
    AFTER Trigger Runs after the change is committed to the table; cannot modify NEW.
    INSTEAD OF Trigger Replaces default behavior on views.


  3. Trigger Function Basics

    4. CREATE OR REPLACE FUNCTION set_created_at()
RETURNS trigger AS
$$
BEGIN
    NEW.created_at := now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


  4. Creating a Trigger

    5. CREATE TRIGGER set_timestamp
BEFORE INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION set_created_at();



  5. Accessing OLD and NEW

    6. NEW.column_name -- the new row (for INSERT/UPDATE)
OLD.column_name -- the old row (for UPDATE/DELETE)



    IF NEW.amount <= 0 THEN
    RAISE EXCEPTION 'Amount must be positive';
END IF;


  6. BEFORE vs AFTER

    7. TypeUsage
    BEFORE
    • Validate data
    • Modify NEW values
    • Reject rows with RAISE EXCEPTION
    AFTER
    • Audit logging
    • Notification
    • Complex cross-table updates


  7. Row-Level vs Statement-Level

    8. FOR EACH ROW       -- fires once per row
FOR EACH STATEMENT -- fires once per statement



  8. INSTEAD OF Triggers (for Views)

    9. CREATE VIEW user_view AS
SELECT id, name FROM users;


    CREATE OR REPLACE FUNCTION user_view_insert()
RETURNS trigger AS
$$
BEGIN
    INSERT INTO users(name) VALUES(NEW.name);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_view_ins
INSTEAD OF INSERT ON user_view
FOR EACH ROW
EXECUTE FUNCTION user_view_insert();


  9. Constraint Triggers vs Normal Triggers

    10. TypeCharacteristics
    Normal Trigger Fires immediately during statement execution
    Constraint Trigger
    • Uses DEFERRABLE
    • Can fire at end of transaction
    • Useful for foreign-key-like logic
    		 

    CREATE CONSTRAINT TRIGGER trg_name
AFTER INSERT ON orders
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION check_order();


  10. Transition Tables (Statement-Level AFTER Triggers)

    11. REFERENCING NEW TABLE AS newrows
REFERENCING OLD TABLE AS oldrows


    CREATE TRIGGER bulk_update_audit
AFTER UPDATE ON accounts
REFERENCING OLD TABLE AS oldset NEW TABLE AS newset
FOR EACH STATEMENT
EXECUTE FUNCTION audit_bulk_update();


    SELECT * FROM newset;
SELECT * FROM oldset;


  11. Enabling and Disabling Triggers

    12. ALTER TABLE users DISABLE TRIGGER set_timestamp;
ALTER TABLE users ENABLE TRIGGER set_timestamp;


    ALTER TABLE users DISABLE TRIGGER ALL;
ALTER TABLE users ENABLE TRIGGER ALL;



  12. Trigger Execution Order

    13. CREATE TRIGGER a_before BEFORE INSERT ON t ...
CREATE TRIGGER z_before BEFORE INSERT ON t ...
-- a_before fires before z_before


  13. Recursive Triggers & Avoiding Infinite Loops

    14. CREATE OR REPLACE FUNCTION update_total()
RETURNS trigger AS
$$
BEGIN
    UPDATE accounts SET total = total + NEW.amount
    WHERE id = NEW.account_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;



    -- Use WHEN clause to guard
CREATE TRIGGER safe_trigger
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.total IS DISTINCT FROM NEW.total)
EXECUTE FUNCTION update_total();


  14. Performance Considerations



  15. Real-World Examples

    16. 1. Auto-updating updated_at 
    CREATE OR REPLACE FUNCTION touch_row()
RETURNS trigger AS
$$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_touch
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION touch_row();


    2. Audit log table 
    CREATE OR REPLACE FUNCTION audit_insert()
RETURNS trigger AS
$$
BEGIN
    INSERT INTO audit(table_name, row_data, action, ts)
    VALUES(TG_TABLE_NAME, row_to_json(NEW), 'insert', now());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_users
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION audit_insert();


    3. Automatically maintain a denormalized counter 
    CREATE OR REPLACE FUNCTION inc_post_count()
RETURNS trigger AS
$$
BEGIN
    UPDATE authors SET post_count = post_count + 1
    WHERE id = NEW.author_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_count_inc
AFTER INSERT ON posts
FOR EACH ROW
EXECUTE FUNCTION inc_post_count();


    4. Prevent updates on archived rows 
    CREATE OR REPLACE FUNCTION prevent_update_archived()
RETURNS trigger AS
$$
BEGIN
    IF OLD.archived THEN
        RAISE EXCEPTION 'Cannot update archived row';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER no_update_archived
BEFORE UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION prevent_update_archived();


More to be expected...