Master PostgreSQL: From Fundamentals to Advanced Database Architecture

This comprehensive set of technical notes covers the architecture of Database Management Systems (DBMS), core relational database concepts, advanced PostgreSQL features (including procedural programming with PL/pgSQL), and practical database schema design.

1. Introduction to Databases & DBMS Architecture

A database is an organized, structured collection of related data points. In real-world software engineering, unorganized data quickly becomes unmanageable. To store, retrieve, and manipulate this data efficiently, we use a Database Management System (DBMS)—a systematic software suite that acts as an intermediary between the physical storage of data and the application layer.

Application Layer (Express API)
       |
       | sends SQL queries via TCP/IP (Port 5432)
       v
Database Management System (PostgreSQL Engine)
       |
       | reads/writes data blocks
       v
Physical Storage (SSD/HDD)

Real-World Analogy: The Human Body Schema

Think of a database schema as the biological blueprint (schema) of a human being. Every human body conforms to a specific structural blueprint: it has a face, two eyes, a nose, an upper body, and a lower body. These are the "columns" or "fields" of the human schema.

Each individual person is an instance (a "row" or "record") created from this identical blueprint. In software engineering, we replicate this real-world paradigm by defining a strict schema before inserting any data.

SQL vs. NoSQL Databases

Databases are broadly categorized into Relational (SQL) and Non-Relational (NoSQL) systems.

Note on Vector Databases: While specialized vector databases exist, modern relational databases like PostgreSQL support vector operations natively through extensions such as pgvector. This allows developers to store machine learning embeddings alongside traditional relational data.

2. PostgreSQL Installation, Setup, and CLI Fundamentals

PostgreSQL is a highly advanced, enterprise-grade, open-source object-relational database system.

Installation Components

When installing PostgreSQL locally, two primary interfaces are provided:

1. pgAdmin 4: A web-based graphical user interface (GUI) for managing databases.
2. psql (SQL Shell): A command-line interface (CLI) for executing raw SQL queries and administrative commands.

By default, PostgreSQL runs on port 5432 with the default superuser username postgres.

Configuring the System Environment Path (Windows)

To execute psql commands directly from your standard terminal (CMD/PowerShell/Bash) without navigating to the installation directory, you must add the PostgreSQL binary path to your system's environment variables:

1. Copy the path to your PostgreSQL binary directory (e.g., C:\Program Files\PostgreSQL\18\bin).
2. Open System Environment Variables -> Click Environment Variables.
3. Under System Variables, select Path and click Edit.
4. Click New and paste the copied path.
5. Restart your terminal.

Essential Interactive Terminal Commands (psql)

These meta-commands are specific to the psql client and are used for database administration:

• \l or \l+: List all databases on the server (with size and description details).
• \c <database_name>: Connect to a specific database.
• \dt or \dt+: List all tables in the currently connected database.
• \d <table_name>: Describe the schema of a specific table (columns, data types, indexes, and constraints).
• \! cls (Windows) or \! clear (Linux/macOS): Clear the terminal screen.
• \q: Quit the psql shell.

3. Core SQL CRUD Operations & Syntax Rules

SQL (Structured Query Language) is case-insensitive regarding keywords, but industry standards dictate writing SQL keywords in UPPERCASE to improve readability and distinguish them from table and column names.

Crucial Syntax Rule:

• Single Quotes (') must be used for string literals and character values (e.g., 'Suraj').
• Double Quotes (") are reserved for system identifiers, such as table or column names that contain special characters or uppercase letters.
• Every SQL statement must end with a semicolon (;) to signal the parser to execute the query.

Realistic CRUD Evolution

1. Creating a Database and Table

-- Create a new database
CREATE DATABASE school;

-- Connect to the database (in psql: \c school)

-- Create a table without constraints
CREATE TABLE students (
    id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    class_name VARCHAR(50),
    marks INT
);

2. Inserting Data (Create)

You can insert a single record or multiple records in a single query execution.

-- Single insert specifying columns
INSERT INTO students (id, first_name, last_name, email, class_name, marks) 
VALUES (1, 'Suraj', 'Jha', 'suraj@example.com', 'MCA First', 95);

-- Multi-row insert
INSERT INTO students (id, first_name, last_name, email, class_name, marks) 
VALUES 
(2, 'Riya', 'Sharma', 'riya@example.com', 'BCA-1', 88),
(3, 'Aman', 'Verma', 'aman@example.com', 'BCA-2', 76),
(4, 'Neha', 'Gupta', 'neha@example.com', 'BCA-1', 92);

3. Querying Data (Read)

-- Read all columns from the table
SELECT * FROM students;

-- Read specific columns to optimize network payload
SELECT first_name, class_name, marks FROM students;

-- Read with a WHERE filter clause
SELECT * FROM students WHERE class_name = 'BCA-1';

4. Updating Data (Update)

Warning: Always specify a WHERE clause when performing an UPDATE or DELETE operation. Omitting the WHERE clause will apply the changes to every single row in the table.

-- Update a specific student's marks
UPDATE students 
SET marks = 91 
WHERE id = 3;

5. Deleting Data (Delete)

-- Delete a specific record
DELETE FROM students 
WHERE id = 4;

4. Advanced PostgreSQL Data Types

PostgreSQL supports a rich set of native data types that extend far beyond standard SQL.

Detailed Data Type Breakdown

• SERIAL: An auto-incrementing integer. PostgreSQL automatically creates a sequence generator behind the scenes and assigns the next sequential integer to this column upon insertion.
• NUMERIC(P, S): Exact numeric storage where P is Precision (total number of digits) and S is Scale (number of digits after the decimal point). Example: NUMERIC(8, 2) can store up to 999999.99.
• VARCHAR(n): Variable-length character string with a limit of n characters. Max size is approximately 10MB.
• TEXT: Unlimited variable-length character string. Max size is 1GB. It is highly optimized in PostgreSQL and has no performance penalty compared to VARCHAR.
• JSONB: Binary JSON storage. Unlike plain JSON (which stores data as a raw string), JSONB decomposes the JSON document into a binary format. This makes insertion slightly slower but enables fast query lookups and support for indexing.
• ARRAY (e.g., TEXT[]): Stores a collection of elements of a single data type in a single column.

Practical Implementation of Advanced Types

-- Create table demonstrating advanced types
CREATE TABLE course_enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    course_name TEXT NOT NULL,
    level VARCHAR(20) CHECK (level IN ('Beginner', 'Intermediate', 'Advanced')),
    price NUMERIC(10, 2) NOT NULL,
    enrolled_on DATE DEFAULT CURRENT_DATE,
    is_completed BOOLEAN DEFAULT FALSE,
    rating INT CHECK (rating BETWEEN 1 AND 5),
    course_meta JSONB,
    skills TEXT[]
);

-- Insert data into advanced columns
INSERT INTO course_enrollments (student_name, email, course_name, level, price, course_meta, skills)
VALUES (
    'Suraj Jha', 
    'suraj@example.com', 
    'Next.js Advanced', 
    'Advanced', 
    299.99, 
    '{"duration": "25 hours", "instructor": "Hitesh Choudhary", "platform": "ChaiCode"}'::jsonb, 
    ARRAY['React', 'Next.js', 'TypeScript']
);

Querying JSONB and Arrays

-- Querying JSONB properties using the ->> operator (returns text)
SELECT student_name, course_meta->>'instructor' AS instructor
FROM course_enrollments
WHERE course_meta->>'platform' = 'ChaiCode';

-- Querying Array columns using the ANY operator
SELECT student_name, skills
FROM course_enrollments
WHERE 'React' = ANY(skills);

5. Constraints: Enforcing Data Integrity

Constraints are rules applied to columns to prevent invalid or low-quality data from entering the database.

• PRIMARY KEY: Uniquely identifies each row in a table. It cannot contain NULL values and must be unique across the table.
• NOT NULL: Enforces that a column cannot accept NULL values.
• UNIQUE: Guarantees that all values in a column are distinct from one another.
• CHECK: Validates that values in a column satisfy a specific boolean condition.
• DEFAULT: Automatically assigns a predefined value if no value is specified during insertion.

Real-World Analogy: Unpaid Internships & Check Constraints

Think of a CHECK constraint as a quality control filter. For example, if you want to prevent unpaid internships in your database, you can add a check constraint: CHECK (salary > 0).

Just as you should never accept an unpaid internship because your time has value, your database should never accept a zero or negative salary value for an active employee.

CREATE TABLE internships (
    internship_id SERIAL PRIMARY KEY,
    company_name VARCHAR(100) NOT NULL,
    role VARCHAR(50) NOT NULL,
    salary NUMERIC(10, 2) NOT NULL CHECK (salary > 0), -- Prevents unpaid roles
    age INT CHECK (age >= 18) -- Age verification check
);

6. Advanced Filtering, Pattern Matching, and Aggregations

Pattern Matching with LIKE and ILIKE

PostgreSQL provides powerful pattern-matching operators using wildcards:

• % represents zero, one, or multiple characters.
• _ represents exactly one character.

-- Find students whose names are exactly 5 characters long and end with 'a'
SELECT student_name FROM course_enrollments 
WHERE student_name LIKE '____a';

Aggregate Functions

Aggregate functions perform calculations on multiple rows and return a single summary value.

• COUNT(): Returns the number of rows.
• SUM(): Calculates the total sum of a numeric column.
• AVG(): Computes the average value.
• MIN() / MAX(): Finds the lowest and highest values.

-- Calculate total revenue and average rating of completed courses
SELECT 
    SUM(price) AS total_revenue,
    AVG(rating) AS average_rating
FROM course_enrollments
WHERE is_completed = TRUE;

Grouping Data with GROUP BY

The GROUP BY clause groups rows that share the same values in specified columns.

Crucial Rule: Any column selected in a SELECT statement that is not part of an aggregate function must be included in the GROUP BY clause.

-- Group by course level and calculate average price
SELECT 
    level, 
    COUNT(*) AS total_students,
    AVG(price) AS average_price
FROM course_enrollments
GROUP BY level;

7. Modifying Schemas with ALTER TABLE

The ALTER TABLE statement is used to modify the structure of an existing table without losing the data already stored inside it.

-- 1. Add a new column
ALTER TABLE course_enrollments ADD COLUMN discount_price NUMERIC(10, 2);

-- 2. Drop an existing column
ALTER TABLE course_enrollments DROP COLUMN discount_price;

-- 3. Rename a column
ALTER TABLE course_enrollments RENAME COLUMN level TO course_level;

-- 4. Modify a column's data type
ALTER TABLE course_enrollments ALTER COLUMN course_level TYPE VARCHAR(50);

-- 5. Set a DEFAULT value for an existing column
ALTER TABLE course_enrollments ALTER COLUMN is_completed SET DEFAULT TRUE;

-- 6. Rename the entire table
ALTER TABLE course_enrollments RENAME TO student_subscriptions;

8. Conditional Logic using CASE Expressions

The CASE expression is SQL’s version of IF-THEN-ELSE conditional logic. It allows you to evaluate conditions and return specific values directly within your queries.

Syntax Structure

CASE 
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ELSE default_result
END AS alias_name

Practical Implementation in Queries

-- Categorize student performance based on marks
SELECT 
    first_name,
    marks,
    CASE 
        WHEN marks >= 90 THEN 'Excellent'
        WHEN marks >= 75 THEN 'First Class'
        WHEN marks >= 50 THEN 'Pass'
        ELSE 'Fail'
    END AS performance_grade
FROM students;

Using CASE in INSERT Statements

You can use CASE to dynamically compute values during an insertion based on incoming parameters:

INSERT INTO course_enrollments (student_name, email, course_name, price, level)
VALUES (
    'Amit Kumar', 
    'amit@example.com', 
    'Docker Essentials', 
    99.00,
    CASE 
        WHEN 99.00 > 200 THEN 'Advanced'
        ELSE 'Beginner'
    END
);

9. Database Relationships & Referential Integrity

Relational databases derive their power from relationships between tables. These relationships are enforced using Primary Keys (PK) and Foreign Keys (FK).

Parent Table (Users: user_id [PK])
       |
       | 1-to-Many Relationship
       v
Child Table (Posts: user_id [FK])

Types of Relationships

1. One-to-One (1:1): Each record in Table A relates to exactly one record in Table B.
   • Implementation: Place a Foreign Key in Table B and mark it as UNIQUE.
   • Example: users and user_profiles.
2. One-to-Many (1:N): A single record in Table A can relate to multiple records in Table B.
   • Implementation: Place a Foreign Key in Table B (the "Many" side) referencing the Primary Key of Table A.
   • Example: directors and movies.
3. Many-to-Many (M:N): Multiple records in Table A can relate to multiple records in Table B.
   • Implementation: Create a third table (junction/join table) containing Foreign Keys pointing to both Table A and Table B.
   • Example: movies and actors (via a movie_cast table).

Referential Actions: ON DELETE CASCADE

When a row in a parent table is deleted, any referencing rows in the child table can become orphaned. To prevent this, we use referential actions:

• ON DELETE CASCADE: Automatically deletes referencing rows in the child table when the parent row is deleted.
• ON DELETE SET NULL: Sets the foreign key column in the child table to NULL when the parent row is deleted.

-- Parent Table
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- Child Table with Cascade Delete
CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    user_id INT REFERENCES users(user_id) ON DELETE CASCADE
);

10. Mastering SQL Joins

Joins are used to combine columns from one or more tables based on a shared relationship between them.

Visualizing Join Types

Setup Tables for Joins

CREATE TABLE classes (
    class_id INT PRIMARY KEY,
    class_name VARCHAR(50) NOT NULL
);

CREATE TABLE join_students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL,
    class_id INT
);

INSERT INTO classes VALUES (101, 'JavaScript'), (102, 'Python'), (103, 'Java');
INSERT INTO join_students VALUES (1, 'Rahul', 101), (2, 'Anjali', 102), (3, 'Aman', 101), (4, 'Neha', NULL);

1. INNER JOIN

Returns only the rows that have matching values in both tables.

SELECT s.student_name, c.class_name
FROM join_students s
INNER JOIN classes c ON s.class_id = c.class_id;
-- Output: Rahul (JS), Anjali (Python), Aman (JS). Neha is omitted because class_id is NULL.

2. LEFT JOIN

Returns all rows from the left table, along with matching rows from the right table. If no match is found, NULL values are returned for the right table's columns.

SELECT s.student_name, c.class_name
FROM join_students s
LEFT JOIN classes c ON s.class_id = c.class_id;
-- Output: Includes Neha with a NULL class_name.

3. RIGHT JOIN

Returns all rows from the right table, along with matching rows from the left table. If no match is found, NULL values are returned for the left table's columns.

SELECT s.student_name, c.class_name
FROM join_students s
RIGHT JOIN classes c ON s.class_id = c.class_id;
-- Output: Includes 'Java' class with a NULL student_name.

4. FULL OUTER JOIN

Returns all rows when there is a match in either the left or right table. Unmatched rows on either side are filled with NULL.

SELECT s.student_name, c.class_name
FROM join_students s
FULL OUTER JOIN classes c ON s.class_id = c.class_id;

5. CROSS JOIN

Returns the Cartesian product of the two tables, pairing every row of the first table with every row of the second table. It does not use an ON clause.

Performance Warning: If Table A has 1,000 rows and Table B has 1,000 rows, a CROSS JOIN will generate $1,000 \times 1,000 = 1,000,000$ rows. Use this join type with extreme caution in production environments.

SELECT s.student_name, c.class_name
FROM join_students s
CROSS JOIN classes c;

6. SELF JOIN

A self join is a regular join in which a table is joined with itself. This is useful for querying hierarchical data stored within a single table.

Real-World Analogy: Employee-Manager Hierarchy

In an organization, every employee has a manager who is also an employee. Instead of creating separate tables for employees and managers, we store everyone in an employees table.

We then use a manager_id column that references the employee_id in the same table. To query this hierarchy, we join the table to itself using aliases.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    manager_id INT REFERENCES employees(employee_id)
);

INSERT INTO employees VALUES 
(1, 'Rahul (CEO)', NULL),
(2, 'Anjali (Manager)', 1),
(3, 'Aman (Developer)', 2);

-- Querying using Self Join
SELECT e.name AS Employee, m.name AS Manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

11. Views, Stored Procedures, and User-Defined Functions (PL/pgSQL)

PostgreSQL supports server-side programming using PL/pgSQL (Procedural Language/PostgreSQL). This allows you to run complex logic directly inside the database engine, reducing network latency between your application and the database.

Views

A view is a virtual table representing the saved result of a query. It does not store physical data itself; instead, it acts as a reusable query wrapper.

-- Create a view for student-class details
CREATE VIEW student_class_details AS
SELECT s.student_name, c.class_name
FROM join_students s
INNER JOIN classes c ON s.class_id = c.class_id;

-- Query the view like a normal table
SELECT * FROM student_class_details;

User-Defined Functions (UDF)

Functions in PostgreSQL must return a value. They are typically used for calculations and data transformations and can be called directly inside SELECT statements.

-- Create a function to categorize channels based on subscriber count
CREATE OR REPLACE FUNCTION get_channel_category(subscribers_million NUMERIC)
RETURNS VARCHAR AS 
$$
BEGIN
    IF subscribers_million >= 1.0 THEN
        RETURN 'Mega Channel';
    ELSE
        RETURN 'Growing Channel';
    END IF;
END;
$$
 LANGUAGE plpgsql;

-- Execute the function
SELECT get_channel_category(1.5);

Stored Procedures

Stored procedures were introduced in PostgreSQL 11. Unlike functions, they do not have to return a value and can manage database transactions (COMMIT and ROLLBACK) internally. They are executed using the CALL command.

-- Create a stored procedure to insert a new class
CREATE OR REPLACE PROCEDURE add_new_class(p_class_id INT, p_class_name VARCHAR)
AS 
$$
BEGIN
    INSERT INTO classes (class_id, class_name) 
    VALUES (p_class_id, p_class_name);
    COMMIT; -- Transaction control
END;
$$
 LANGUAGE plpgsql;

-- Execute the stored procedure
CALL add_new_class(104, 'Rust');

Key Differences: Functions vs. Procedures

12. Real-World Case Study: Designing a Banking System Database

This section outlines the step-by-step database design process for a robust, production-ready banking system.

Step 1: System Requirements & Scope

A standard banking system must support the following core operations:

1. Customers: Registering and managing customer profiles.
2. Accounts: Opening multiple bank accounts (Savings, Current) per customer across various branches.
3. Transactions: Recording secure deposits, withdrawals, and transfers between accounts.
4. Loans: Issuing loans to customers and tracking their repayment schedules (EMIs).
5. Branches: Managing physical branch locations and their unique identifiers (IFSC codes).

Step 2: Entity-Relationship (ER) Diagram & Schema Mapping

Step 3: Complete SQL DDL Implementation

-- 1. Create Branches Table
CREATE TABLE branches (
    branch_id SERIAL PRIMARY KEY,
    branch_name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL,
    ifsc_code VARCHAR(11) UNIQUE NOT NULL CHECK (LENGTH(ifsc_code) = 11),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Create Customers Table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(15) UNIQUE NOT NULL,
    date_of_birth DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create Custom ENUM Types for Accounts and Transactions
CREATE TYPE account_type_enum AS ENUM ('Savings', 'Current', 'Salary');
CREATE TYPE transaction_type_enum AS ENUM ('Deposit', 'Withdrawal', 'Transfer');
CREATE TYPE transaction_status_enum AS ENUM ('Pending', 'Success', 'Failed');
CREATE TYPE loan_type_enum AS ENUM ('Home', 'Personal', 'Educational', 'Car');
CREATE TYPE loan_status_enum AS ENUM ('Active', 'Closed', 'Defaulted');

-- 3. Create Accounts Table
CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    branch_id INT REFERENCES branches(branch_id) ON DELETE RESTRICT,
    balance NUMERIC(15, 2) NOT NULL DEFAULT 0.00 CHECK (balance >= 0.00),
    account_type account_type_enum NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. Create Transactions Table
CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    account_id INT REFERENCES accounts(account_id) ON DELETE CASCADE,
    transaction_type transaction_type_enum NOT NULL,
    amount NUMERIC(15, 2) NOT NULL CHECK (amount > 0.00),
    status transaction_status_enum DEFAULT 'Pending',
    transaction_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 5. Create Loans Table
CREATE TABLE loans (
    loan_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    loan_type loan_type_enum NOT NULL,
    loan_amount NUMERIC(15, 2) NOT NULL CHECK (loan_amount > 0.00),
    interest_rate NUMERIC(5, 2) NOT NULL CHECK (interest_rate >= 0.00),
    status loan_status_enum DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 6. Create Loan Payments (EMIs) Table
CREATE TABLE loan_payments (
    payment_id SERIAL PRIMARY KEY,
    loan_id INT REFERENCES loans(loan_id) ON DELETE CASCADE,
    installment_amount NUMERIC(15, 2) NOT NULL CHECK (installment_amount > 0.00),
    due_date DATE NOT NULL,
    amount_paid NUMERIC(15, 2) DEFAULT 0.00 CHECK (amount_paid >= 0.00),
    amount_due NUMERIC(15, 2) NOT NULL CHECK (amount_due >= 0.00),
    payment_date DATE
);

Step 4: Building an Express.js API with Connection Pooling

To connect our Node.js application to the PostgreSQL database, we use the pg library. We configure a Connection Pool to manage and reuse database connections efficiently, rather than opening a new connection for every single request.

import express from 'express';
import pkg from 'pg';
import dotenv from 'dotenv';

dotenv.config();
const { Pool } = pkg;

const app = express();
app.use(express.json());

// Configure PostgreSQL Connection Pool
const pool = new Pool({
    user: process.env.DB_USER,
    host: process.env.DB_HOST,
    database: process.env.DB_NAME,
    password: process.env.DB_PASSWORD,
    port: process.env.DB_PORT,
});

// Test Database Connection on Startup
pool.connect((err, client, release) => {
    if (err) {
        console.error('❌ Error connecting to the database:', err.stack);
    } else {
        console.log('✅ Connected to PostgreSQL successfully.');
        release(); // Release the client back to the pool
    }
});

// API Endpoint: Get All Active Accounts with Customer Details
app.get('/api/accounts', async (req, res) => {
    try {
        const queryText = `
            SELECT 
                a.account_id,
                c.first_name,
                c.last_name,
                a.balance,
                a.account_type,
                b.branch_name
            FROM accounts a
            INNER JOIN customers c ON a.customer_id = c.customer_id
            INNER JOIN branches b ON a.branch_id = b.branch_id
            WHERE a.is_active = TRUE
            ORDER BY a.balance DESC;
        `;
        const result = await pool.query(queryText);
        res.status(200).json({
            success: true,
            count: result.rowCount,
            data: result.rows
        });
    } catch (error) {
        console.error('Error executing query:', error);
        res.status(500).json({ success: false, error: 'Internal Server Error' });
    }
});

const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
    console.log(`🚀 Server is running on http://localhost:${PORT}`);
});

Summary and Key Takeaways

• Relational Integrity: Relational databases rely on strict schemas and constraints (PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL) to guarantee data quality and prevent invalid entries.
• PostgreSQL Syntax Rules: Always use single quotes (') for string values and double quotes (") for system identifiers. Every SQL statement must end with a semicolon (;).
• Connection Pooling: In production Node.js applications, always use a connection pool (like pg.Pool) instead of single client connections. This allows connections to be reused efficiently and prevents database resource exhaustion.
• Advanced Data Types: PostgreSQL natively supports semi-structured data types like JSONB and collections like ARRAY. This provides NoSQL-like flexibility while maintaining full relational integrity.
• Joins: Use INNER JOIN to retrieve matching records from both tables, and LEFT JOIN to preserve all records from the primary table while filling missing relationships with NULL.
• PL/pgSQL: Stored procedures and user-defined functions allow you to run complex business logic directly on the database server. This reduces network roundtrips and improves application performance.
• Database Design: When designing a database schema, first identify the core entities and their relationships. Use foreign keys to link tables, and apply constraints to enforce business rules automatically at the database level.