PostgreSQL Questions and Answers
Question 1: What is the difference between VARCHAR and TEXT data types in
PostgreSQL?
Answer: In PostgreSQL, both VARCHAR and TEXT are used to store variable-length character strings, but
they have some key differences:
VARCHAR(n): Has a specified maximum length limit (n characters). If you try to insert a string longer
than n, PostgreSQL will raise an error.
VARCHAR (without length specifier): Functionally identical to TEXT - no length limit.
TEXT: No length limit, can store strings up to 1 GB in size.
Performance: There's virtually no performance difference between VARCHAR and TEXT in PostgreSQL.
Both are stored the same way internally.
Example:
sql
CREATE TABLE example (
limited_field VARCHAR(50), -- Max 50 characters
unlimited_field VARCHAR, -- No limit (same as TEXT)
text_field TEXT -- No limit
);
Question 2: Explain the difference between INNER JOIN, LEFT JOIN, and
RIGHT JOIN with examples.
Answer: JOINs are used to combine rows from multiple tables based on related columns:
INNER JOIN: Returns only rows that have matching values in both tables. LEFT JOIN: Returns all rows
from the left table and matched rows from the right table. NULL values for non-matching right table
columns. RIGHT JOIN: Returns all rows from the right table and matched rows from the left table. NULL
values for non-matching left table columns.
Example:
sql
-- Sample tables
CREATE TABLE customers (id INT, name VARCHAR(50));
CREATE TABLE orders (id INT, customer_id INT, product VARCHAR(50));
INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO orders VALUES (1, 1, 'Laptop'), (2, 1, 'Mouse'), (3, 2, 'Keyboard');
-- INNER JOIN - Only customers with orders
SELECT c.name, o.product
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;
-- Result: Alice-Laptop, Alice-Mouse, Bob-Keyboard
-- LEFT JOIN - All customers, including those without orders
SELECT c.name, o.product
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
-- Result: Alice-Laptop, Alice-Mouse, Bob-Keyboard, Charlie-NULL
-- RIGHT JOIN - All orders, including those without valid customers
SELECT c.name, o.product
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;
-- Same as INNER JOIN in this case since all orders have valid customer_ids
Question 3: What are PostgreSQL indexes and why are they important? Name
different types of indexes.
Answer: Indexes are database objects that improve query performance by creating shortcuts to data.
They work like a book's index - instead of scanning every page, you can jump directly to relevant sections.
Why indexes are important:
Dramatically speed up SELECT queries with WHERE clauses
Speed up JOIN operations
Speed up ORDER BY operations
Enforce uniqueness constraints
Types of indexes in PostgreSQL:
1. B-Tree Index (default): Best for equality and range queries
2. Hash Index: Good for equality comparisons only
3. GiST Index: Generalized Search Tree for complex data types
4. SP-GiST Index: Space-partitioned GiST for non-balanced data structures
5. GIN Index: Generalized Inverted Index for composite values (arrays, full-text search)
6. BRIN Index: Block Range Index for very large tables with natural ordering
Example:
sql
-- Create different types of indexes
CREATE INDEX idx_customer_name ON customers USING btree (name);
CREATE INDEX idx_customer_id_hash ON customers USING hash (id);
CREATE INDEX idx_order_date_brin ON orders USING brin (order_date);
-- Unique index
CREATE UNIQUE INDEX idx_customer_email ON customers (email);
Question 4: Explain PostgreSQL transactions and ACID properties with
examples.
Answer: A transaction is a sequence of database operations that are treated as a single unit of work.
PostgreSQL follows ACID properties:
ACID Properties:
Atomicity: All operations in a transaction succeed or all fail
Consistency: Database remains in a valid state before and after transaction
Isolation: Concurrent transactions don't interfere with each other
Durability: Committed changes persist even after system failure
Transaction Control Commands:
BEGIN or START TRANSACTION : Start a transaction
COMMIT : Save all changes permanently
ROLLBACK : Undo all changes in the transaction
SAVEPOINT : Create a point to rollback to within a transaction
Example:
sql
-- Bank transfer example demonstrating ACID properties
BEGIN;
-- Deduct from account A
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 'A001';
-- Add to account B
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 'B001';
-- Check if both operations succeeded
-- If any error occurs, ROLLBACK undoes both operations
COMMIT; -- or ROLLBACK;
-- Using savepoints
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A001';
SAVEPOINT sp1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B001';
-- If this fails, we can rollback to sp1
ROLLBACK TO SAVEPOINT sp1;
COMMIT;
Question 5: What are PostgreSQL stored procedures and functions? What's the
difference between them?
Answer: Both stored procedures and functions are reusable blocks of code stored in the database, but
they have important differences:
Functions:
Must return a value
Can be used in SELECT statements and expressions
Cannot manage transactions (COMMIT/ROLLBACK)
Called using SELECT function_name()
Stored Procedures:
May or may not return values
Cannot be used in SELECT statements
Can manage transactions
Called using CALL procedure_name()
Examples:
Function Example:
sql
-- Function to calculate total order amount
CREATE OR REPLACE FUNCTION calculate_order_total(order_id INT)
RETURNS DECIMAL(10,2)
LANGUAGE plpgsql
AS $$
DECLARE
total DECIMAL(10,2);
BEGIN
SELECT SUM(quantity * price) INTO total
FROM order_items
WHERE order_id = calculate_order_total.order_id;
RETURN COALESCE(total, 0);
END;
$$;
-- Usage in SELECT
SELECT order_id, calculate_order_total(order_id) as total
FROM orders;
Stored Procedure Example:
sql
-- Procedure to process order with transaction management
CREATE OR REPLACE PROCEDURE process_order(
p_customer_id INT,
p_product_id INT,
p_quantity INT
)
LANGUAGE plpgsql
AS $$
BEGIN
-- Start transaction management
INSERT INTO orders (customer_id, order_date)
VALUES (p_customer_id, CURRENT_DATE);
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (currval('orders_id_seq'), p_product_id, p_quantity);
-- Update inventory
UPDATE products
SET stock_quantity = stock_quantity - p_quantity
WHERE product_id = p_product_id;
-- Commit is automatic unless error occurs
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
$$;
-- Usage with CALL
CALL process_order(1, 101, 5);
