PostgreSQL Command Reference

SQL Basics

(Basic SQL syntax rules. SQL is generally case-insensitive for keywords, although it's good practice to use uppercase for keywords for readability. Identifiers (table and column names) might be case-sensitive depending on configuration, but it is best to stick to lower case for identifiers for consistency.)

Data Definition Language (DDL)

(Use CREATE TABLE, ALTER TABLE, DROP TABLE to define the structure of your relational data.)

Index Management

CREATE INDEX idx_attribute_name ON table_name (attribute_name); -- Basic index
CREATE INDEX table_name_attribute_name_idx ON table_name(attribute_name); -- Explicitly named index (consistent naming convention)

View Management

CREATE VIEW daily_report AS
SELECT
    ph.invoice_no,
    ph.purchase_date,
    i.name
FROM
    purchase_headers ph
JOIN
    purchase_details pd ON pd.purchase_id = ph.purchase_id
JOIN
    items i ON i.item_id = pd.item_id;

SELECT * FROM daily_report; -- Querying the view

Stored Procedure Management

CREATE OR REPLACE PROCEDURE decrement_item(in_item_id INT)
LANGUAGE plpgsql
AS $$BEGIN
    UPDATE items
    SET quantity = quantity - 1
    WHERE id = in_item_id;
END;$$;

Data Manipulation Language (DML)

SELECT (Querying Data)

SELECT * FROM customers; -- Select all columns and rows

String Operations

SELECT CONCAT(first_name, ' ', last_name) FROM customers;    -- Concatenate strings
SELECT COALESCE(phone_number, 'N/A') FROM customers;      -- Handle NULL values
SELECT CONCAT(NULL, phone_number) from customers;          -- Illustration with CONCAT and NULL (result will be NULL)

Conditional Logic (CASE)

SELECT
    CASE
        WHEN phone_number > 100 THEN 'High Number'  --Corrected Syntax
        ELSE 'Normal'
    END
FROM customers;

Filtering (WHERE Clause)

SELECT * FROM customers WHERE name LIKE 'A%';       -- Starts with 'A' (case-sensitive)
SELECT * FROM customers WHERE name ILIKE 'a%';      -- Starts with 'a' (case-insensitive)
SELECT * FROM customers WHERE deleted_at IS NOT NULL;  -- Check for non-NULL values
SELECT * FROM customers WHERE name IN ('A', 'B');    -- Match values in a list

Sorting (ORDER BY)

SELECT * FROM customers ORDER BY name ASC;    -- Ascending order (default)
SELECT * FROM customers ORDER BY name DESC;   -- Descending order

Limiting Results (LIMIT)

SELECT * FROM customers ORDER BY id LIMIT 5;  -- First 5 results, ordered by ID (important for predictable results)

Skipping Results (OFFSET)

SELECT * FROM customers ORDER BY id LIMIT 5 OFFSET 5; -- Skip the first 5, then return the next 5

Grouping and Aggregating (GROUP BY, HAVING)

SELECT
    item_category_id,
    COUNT(item_id)
FROM
    items
GROUP BY
    item_category_id;

SELECT
    item_category_id,
    COUNT(item_id)
FROM
    items
GROUP BY
    item_category_id
HAVING
    COUNT(item_id) > 2;  -- Filter groups where the count is greater than 2

Subqueries

SELECT
    ic.item_category_id,
    (SELECT COUNT(*) FROM items i WHERE i.item_category_id = ic.item_category_id) AS item_count --Added alias
FROM
    item_categories ic; --Simplified example focusing on correlation

Joining Tables

INNER JOIN

SELECT * FROM items i JOIN item_categories ic ON i.item_category_id = ic.item_category_id; --Return matching rows from both table

LEFT JOIN (LEFT OUTER JOIN)

SELECT * FROM item_categories ic LEFT JOIN items i ON i.item_category_id = ic.item_category_id; --All from item_categories, matching from items.

RIGHT JOIN (RIGHT OUTER JOIN)

SELECT * FROM items i RIGHT JOIN item_categories ic ON i.item_category_id = ic.item_category_id; -- All from items, matching from item_categories.

CROSS JOIN

(CROSS JOIN is a Cartesian Product of the table. It matches each row of one table to each row of another table).

SELECT * FROM table1 CROSS JOIN table2;

Modifying Data

INSERT

INSERT INTO items (name, price) VALUES ('test', 15000); -- Inserts a new item

UPDATE

UPDATE customers SET phone_number = '555-1212' WHERE id = 1; -- Example update

Soft Delete

UPDATE customers SET deleted_at = current_timestamp WHERE id = 1;

DELETE

DELETE FROM customers WHERE id = 1;  -- Hard delete: removes the row permanently

Transaction Management

ACID Properties

Property	Description
Atomicity	All operations in the transaction succeed, or the entire transaction is rolled back (nothing is applied).
Consistency	The transaction must maintain the integrity of the database, moving from one valid state to another.
Isolation	Concurrent transactions should not interfere with each other; each transaction should appear isolated.
Durability	Once a transaction is committed, the changes are permanent, even in the event of a system failure.

Transaction Isolation Levels

Isolation levels control the degree to which concurrent transactions are isolated from each other. Higher isolation levels reduce concurrency but provide greater data consistency.

• Read Uncommitted: The lowest level. Transactions can read uncommitted changes from other transactions (dirty reads). Rarely used.

• Read Committed: Transactions can only read committed data. Prevents dirty reads. This is the default isolation level for PostgreSQL.

• Repeatable Read: A transaction reading the same data multiple times will see the same values, even if other transactions commit changes. Prevents dirty reads and non-repeatable reads (but not phantom reads).

• Serializable: The highest level. Guarantees that concurrent transactions will behave as if they were executed serially, preventing dirty reads, non-repeatable reads, and phantom reads.

Concurrency Issues and How Isolation Levels Address Them

Issue	Description	Read Uncommitted	Read Committed	Repeatable Read	Serializable
Dirty Read	Reading uncommitted changes from another transaction.	Possible	Prevented	Prevented	Prevented
Non-Repeatable Read	Reading the same row twice within a transaction yields different results.	Possible	Possible	Prevented	Prevented
Phantom Read	New rows appear in query results due to other transaction's inserts/deletes.	Possible	Possible	Possible	Prevented

Explanation of Isolation Level Details

• Read Uncommitted: Allows dirty reads, meaning a transaction can see uncommitted changes made by another transaction. This is generally avoided as it can lead to inconsistent and incorrect data.

• Read Committed: Prevents dirty reads. A transaction can only see data that has been committed by other transactions. However, non-repeatable reads are still possible, as another transaction can commit changes between two reads within the same transaction. This is PostgreSQL's default isolation.

• Repeatable Read: Prevents both dirty reads and non-repeatable reads. A transaction reading the same row multiple times during its execution will see the same data. However, phantom reads are still possible if another transaction inserts new rows that would match the query conditions of the first transaction.

• Serializable: The highest level of isolation. It prevents all concurrency issues, ensuring that transactions behave as if they were executed serially (one after another). Achieving serializability often involves locking resources, which can reduce concurrency and potentially lead to performance issues.

Setting Transaction Isolation Level Example

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- Your transaction operations here
COMMIT;

Important Considerations for Transactions:

• Always use transactions for any sequence of database modifications that must be treated as a single unit of work.
• Choose the appropriate isolation level based on the trade-off between data consistency and concurrency requirements. The default (Read Committed) is often a good starting point.
• Keep transactions as short as possible to minimize locking and contention.
• Handle potential errors and roll back transactions if necessary to ensure data integrity.