PostgreSQL Comprehensive Learning Guide

Table of Contents
1. Introduction & Setup

2. Basic Concepts

3. Database Operations

4. Table Operations

5. Data Types

6. CRUD Operations

7. Querying Data

8. Joins

9. Aggregate Functions

10. Constraints

11. Indexes

12. Views

13. Transactions

14. Advanced Features

15. Performance & Optimization

Introduction & Setup

What is PostgreSQL?
PostgreSQL is a powerful, open-source object-relational database system with a strong reputation for reliability,
feature robustness, and performance.

Installation

bash
 # Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib

# macOS (using Homebrew)

brew install postgresql

# Windows
# Download installer from [Link]

Accessing PostgreSQL

bash

# Connect to PostgreSQL
psql -U postgres

# Connect to specific database

psql -U username -d database_name

# Connect to remote server

psql -h hostname -U username -d database_name

Basic psql Commands

sql

\l -- List all databases

\c database_name -- Connect to a database
\dt -- List all tables
\d table_name -- Describe table structure
\du -- List all users
\q -- Quit psql
\? -- Help
\h SQL_COMMAND -- SQL command help

Basic Concepts

Database Hierarchy

Server (Cluster)
└── Databases
└── Schemas (default: public)
 └── Tables
└── Columns/Rows

SQL Categories
DDL (Data Definition Language): CREATE, ALTER, DROP

DML (Data Manipulation Language): INSERT, UPDATE, DELETE

DQL (Data Query Language): SELECT

DCL (Data Control Language): GRANT, REVOKE

TCL (Transaction Control Language): COMMIT, ROLLBACK

Database Operations

Create Database

sql

CREATE DATABASE my_database;

-- With options
CREATE DATABASE my_database
OWNER = myuser
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8';

Drop Database

sql

DROP DATABASE my_database;

DROP DATABASE IF EXISTS my_database;

Alter Database

sql

ALTER DATABASE my_database RENAME TO new_name;

ALTER DATABASE my_database OWNER TO new_owner;
Table Operations

Create Table

sql

CREATE TABLE users (

id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
age INTEGER,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Create Table from Query

sql

CREATE TABLE active_users AS

SELECT * FROM users WHERE is_active = true;

Alter Table

sql

-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Rename column
ALTER TABLE users RENAME COLUMN username TO user_name;

-- Change column type

ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

-- Set default value

ALTER TABLE users ALTER COLUMN is_active SET DEFAULT false;

-- Rename table
ALTER TABLE users RENAME TO app_users;
Drop Table

sql

DROP TABLE users;

DROP TABLE IF EXISTS users;
DROP TABLE users CASCADE; -- Also drop dependent objects

Truncate Table

sql

TRUNCATE TABLE users; -- Delete all rows, faster than DELETE

TRUNCATE TABLE users RESTART IDENTITY; -- Also reset sequences

Data Types

Numeric Types

sql

SMALLINT -- -32768 to 32767

INTEGER or INT -- -2147483648 to 2147483647
BIGINT -- Large integers
DECIMAL(p,s) -- Exact decimal, p=precision, s=scale
NUMERIC(p,s) -- Same as DECIMAL
REAL -- Floating-point (6 decimal digits)
DOUBLE PRECISION -- Floating-point (15 decimal digits)
SERIAL -- Auto-incrementing integer
BIGSERIAL -- Auto-incrementing bigint

Character Types

sql

CHAR(n) -- Fixed-length string

VARCHAR(n) -- Variable-length string with limit
TEXT -- Unlimited length string

Date/Time Types

sql
 DATE -- Date only (YYYY-MM-DD)
TIME -- Time only (HH:MM:SS)
TIMESTAMP -- Date and time
TIMESTAMPTZ -- Timestamp with timezone
INTERVAL -- Time interval

Boolean

sql

BOOLEAN -- true, false, null

Binary Data

sql

BYTEA -- Binary data

JSON Types

sql

JSON -- Text-based JSON

JSONB -- Binary JSON (faster, supports indexing)

Array Types

sql

INTEGER[] -- Array of integers

TEXT[] -- Array of text

Other Types

sql

UUID -- Universally unique identifier

INET -- IP address
MACADDR -- MAC address
CIDR -- Network address

CRUD Operations

INSERT - Create Data

 sql

-- Insert single row

INSERT INTO users (username, email, password, age)
VALUES ('john_doe', 'john@[Link]', 'hashed_pwd', 25);

-- Insert multiple rows

INSERT INTO users (username, email, password, age)
VALUES
('jane_doe', 'jane@[Link]', 'hashed_pwd', 28),
('bob_smith', 'bob@[Link]', 'hashed_pwd', 32);

-- Insert and return inserted data

INSERT INTO users (username, email, password)
VALUES ('alice', 'alice@[Link]', 'hashed_pwd')
RETURNING id, username, created_at;

-- Insert from another table

INSERT INTO archived_users
SELECT * FROM users WHERE created_at < '2020-01-01';

SELECT - Read Data

sql
 -- Select all columns
SELECT * FROM users;

-- Select specific columns

SELECT username, email FROM users;

-- With conditions
SELECT * FROM users WHERE age > 25;

-- With multiple conditions

SELECT * FROM users
WHERE age > 25 AND is_active = true;

-- Pattern matching
SELECT * FROM users WHERE email LIKE '%@[Link]';

-- In list
SELECT * FROM users WHERE age IN (25, 30, 35);

-- Between range
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- Null check
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

UPDATE - Modify Data

sql
 -- Update single column
UPDATE users SET is_active = false WHERE id = 1;

-- Update multiple columns

UPDATE users
SET email = 'newemail@[Link]', age = 26
WHERE username = 'john_doe';

-- Update with calculation

UPDATE users SET age = age + 1;

-- Update and return

UPDATE users SET is_active = true
WHERE id = 1
RETURNING *;

-- Update from another table

UPDATE users u
SET is_active = false
FROM banned_users b
WHERE [Link] = b.user_id;

DELETE - Remove Data

sql

-- Delete specific rows

DELETE FROM users WHERE id = 1;

-- Delete with condition

DELETE FROM users WHERE is_active = false;

-- Delete all rows (use with caution!)

DELETE FROM users;

-- Delete and return deleted data

DELETE FROM users WHERE id = 1 RETURNING *;

-- Delete with subquery

DELETE FROM users
WHERE id IN (SELECT user_id FROM banned_users);
Querying Data

ORDER BY - Sorting

sql

-- Ascending order (default)

SELECT * FROM users ORDER BY age;
SELECT * FROM users ORDER BY age ASC;

-- Descending order
SELECT * FROM users ORDER BY age DESC;

-- Multiple columns
SELECT * FROM users ORDER BY age DESC, username ASC;

-- With NULL handling

SELECT * FROM users ORDER BY age NULLS FIRST;
SELECT * FROM users ORDER BY age NULLS LAST;

LIMIT and OFFSET - Pagination

sql

-- Get first 10 rows

SELECT * FROM users LIMIT 10;

-- Skip first 20, get next 10

SELECT * FROM users LIMIT 10 OFFSET 20;

-- Alternative syntax
SELECT * FROM users OFFSET 20 LIMIT 10;

DISTINCT - Remove Duplicates

sql

SELECT DISTINCT age FROM users;

SELECT DISTINCT age, is_active FROM users;

-- Count distinct
SELECT COUNT(DISTINCT age) FROM users;

WHERE Operators

sql
 -- Comparison operators
=, <>, !=, <, >, <=, >=

-- Logical operators
AND, OR, NOT

-- Pattern matching
LIKE, ILIKE (case-insensitive), NOT LIKE

-- Wildcards
% (any characters), _ (single character)

-- Examples
SELECT * FROM users WHERE username LIKE 'john%';
SELECT * FROM users WHERE email ILIKE '%[Link]';

-- Regular expressions
SELECT * FROM users WHERE email ~ '^[a-z]+@gmail\.com$';
SELECT * FROM users WHERE email ~* '^[A-Z]+@GMAIL\.COM$'; -- case-insensitive

Subqueries

sql

-- Subquery in WHERE
SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- Subquery in FROM
SELECT avg_age FROM (
SELECT AVG(age) as avg_age FROM users
) AS subquery;

-- Correlated subquery
SELECT [Link], [Link]
FROM users u
WHERE [Link] > (
SELECT AVG(age) FROM users WHERE is_active = u.is_active
);

-- EXISTS
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = [Link]
);
Joins

INNER JOIN

sql

-- Returns only matching rows from both tables

SELECT [Link], o.order_date, [Link]
FROM users u
INNER JOIN orders o ON [Link] = o.user_id;

LEFT JOIN (LEFT OUTER JOIN)

sql

-- Returns all rows from left table, matching rows from right
SELECT [Link], o.order_date
FROM users u
LEFT JOIN orders o ON [Link] = o.user_id;

RIGHT JOIN (RIGHT OUTER JOIN)

sql

-- Returns all rows from right table, matching rows from left
SELECT [Link], o.order_date
FROM users u
RIGHT JOIN orders o ON [Link] = o.user_id;

FULL OUTER JOIN

sql

-- Returns all rows from both tables

SELECT [Link], o.order_date
FROM users u
FULL OUTER JOIN orders o ON [Link] = o.user_id;

CROSS JOIN

sql
 -- Cartesian product of both tables
SELECT [Link], p.product_name
FROM users u
CROSS JOIN products p;

SELF JOIN

sql

-- Join table to itself

SELECT [Link] as employee, [Link] as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = [Link];

Multiple Joins

sql

SELECT [Link], o.order_date, p.product_name

FROM users u
INNER JOIN orders o ON [Link] = o.user_id
INNER JOIN order_items oi ON [Link] = oi.order_id
INNER JOIN products p ON oi.product_id = [Link];

Aggregate Functions

Basic Aggregates

sql

COUNT(*) -- Count all rows

COUNT(column) -- Count non-null values
SUM(column) -- Sum of values
AVG(column) -- Average
MIN(column) -- Minimum value
MAX(column) -- Maximum value

Examples

sql
 -- Count users
SELECT COUNT(*) FROM users;
SELECT COUNT(*) as total_users FROM users;

-- Average age
SELECT AVG(age) FROM users;

-- Min, Max
SELECT MIN(age), MAX(age) FROM users;

-- Sum
SELECT SUM(total) FROM orders;

GROUP BY

sql

-- Count users by age

SELECT age, COUNT(*) as count
FROM users
GROUP BY age;

-- Average order total by user

SELECT user_id, AVG(total) as avg_order
FROM orders
GROUP BY user_id;

-- Multiple grouping columns

SELECT is_active, age, COUNT(*)
FROM users
GROUP BY is_active, age;

HAVING - Filter Groups

sql
 -- Users with age appearing more than 5 times
SELECT age, COUNT(*) as count
FROM users
GROUP BY age
HAVING COUNT(*) > 5;

-- Average order total > 100

SELECT user_id, AVG(total) as avg_total
FROM orders
GROUP BY user_id
HAVING AVG(total) > 100;

String Aggregation

sql

-- Concatenate values
SELECT STRING_AGG(username, ', ') as all_users
FROM users;

-- Array aggregation
SELECT ARRAY_AGG(username) as user_array
FROM users;

Constraints

PRIMARY KEY

sql
 -- Single column
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50)
);

-- Composite primary key

CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
PRIMARY KEY (order_id, product_id)
);

-- Add primary key to existing table

ALTER TABLE users ADD PRIMARY KEY (id);

FOREIGN KEY

sql

-- Create with foreign key

CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
order_date DATE
);

-- With cascade options

CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);

-- Add foreign key to existing table

ALTER TABLE orders
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id);

UNIQUE

sql
 -- Single column
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(100) UNIQUE
);

-- Multiple columns (composite unique)

CREATE TABLE users (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
UNIQUE (first_name, last_name)
);

-- Add unique constraint

ALTER TABLE users ADD UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

NOT NULL

sql

CREATE TABLE users (

id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
);

-- Add NOT NULL

ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Remove NOT NULL

ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

CHECK

sql
 -- Age constraint
CREATE TABLE users (
id SERIAL PRIMARY KEY,
age INTEGER CHECK (age >= 18)
);

-- Multiple conditions
CREATE TABLE products (
id SERIAL PRIMARY KEY,
price DECIMAL CHECK (price > 0),
discount DECIMAL CHECK (discount >= 0 AND discount <= 100)
);

-- Named constraint
CREATE TABLE users (
id SERIAL PRIMARY KEY,
age INTEGER,
CONSTRAINT valid_age CHECK (age >= 18 AND age <= 120)
);

-- Add CHECK constraint

ALTER TABLE users ADD CHECK (age >= 18);

DEFAULT

sql

CREATE TABLE users (

id SERIAL PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT true,
role VARCHAR(20) DEFAULT 'user'
);

-- Add default to existing column

ALTER TABLE users ALTER COLUMN is_active SET DEFAULT true;

-- Remove default
ALTER TABLE users ALTER COLUMN is_active DROP DEFAULT;
Indexes

Create Index

sql

-- Basic index
CREATE INDEX idx_users_email ON users(email);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Composite index
CREATE INDEX idx_users_name ON users(first_name, last_name);

-- Partial index
CREATE INDEX idx_active_users ON users(email)
WHERE is_active = true;

-- Expression index
CREATE INDEX idx_lower_email ON users(LOWER(email));

Index Types

sql

-- B-tree (default, good for most cases)

CREATE INDEX idx_name ON table_name(column) USING btree;

-- Hash (good for equality comparisons)

CREATE INDEX idx_name ON table_name(column) USING hash;

-- GiST (good for geometric data, full-text search)

CREATE INDEX idx_name ON table_name(column) USING gist;

-- GIN (good for arrays, JSONB, full-text search)

CREATE INDEX idx_name ON table_name USING gin(column);

Drop Index

sql

DROP INDEX idx_users_email;

DROP INDEX IF EXISTS idx_users_email;
List Indexes

sql

-- List all indexes in current database

\di

-- Query indexes for a table

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

Views

Create View

sql

-- Simple view
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE is_active = true;

-- Complex view with joins

CREATE VIEW user_orders AS
SELECT
[Link],
o.order_date,
[Link]
FROM users u
INNER JOIN orders o ON [Link] = o.user_id;

-- View with aggregation

CREATE VIEW user_stats AS
SELECT
user_id,
COUNT(*) as order_count,
SUM(total) as total_spent
FROM orders
GROUP BY user_id;

Query View

sql
 -- Use like a regular table
SELECT * FROM active_users;
SELECT * FROM user_orders WHERE order_date > '2024-01-01';

Materialized Views

sql

-- Create materialized view (stores data physically)

CREATE MATERIALIZED VIEW user_stats_mv AS
SELECT
user_id,
COUNT(*) as order_count,
SUM(total) as total_spent
FROM orders
GROUP BY user_id;

-- Refresh materialized view

REFRESH MATERIALIZED VIEW user_stats_mv;

-- Refresh concurrently (doesn't block reads)

REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats_mv;

Drop View

sql

DROP VIEW active_users;

DROP VIEW IF EXISTS active_users;
DROP MATERIALIZED VIEW user_stats_mv;

Replace View

sql

CREATE OR REPLACE VIEW active_users AS

SELECT id, username, email, created_at
FROM users
WHERE is_active = true;
Transactions

Basic Transaction

sql

-- Start transaction
BEGIN;
-- or
START TRANSACTION;

-- Your SQL statements

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

-- Commit changes
COMMIT;

-- Or rollback if there's an error

ROLLBACK;

Transaction Example

sql

BEGIN;

INSERT INTO orders (user_id, total) VALUES (1, 150.00);

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 100;

-- If everything is okay
COMMIT;

-- If something went wrong

-- ROLLBACK;

Savepoints

sql
 BEGIN;

UPDATE users SET age = 30 WHERE id = 1;

SAVEPOINT my_savepoint;

UPDATE users SET age = 40 WHERE id = 2;

-- Rollback to savepoint (undoes second update only)

ROLLBACK TO SAVEPOINT my_savepoint;

COMMIT;

Transaction Isolation Levels

sql

-- Read Uncommitted (not supported in PostgreSQL)

-- Read Committed (default)
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Repeatable Read
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Serializable
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Advanced Features

Common Table Expressions (CTE)

sql
 -- Simple CTE
WITH active_users AS (
SELECT * FROM users WHERE is_active = true
)
SELECT * FROM active_users WHERE age > 25;

-- Multiple CTEs
WITH
active_users AS (
SELECT * FROM users WHERE is_active = true
),
recent_orders AS (
SELECT * FROM orders WHERE order_date > '2024-01-01'
)
SELECT [Link], [Link]
FROM active_users u
JOIN recent_orders o ON [Link] = o.user_id;

-- Recursive CTE (e.g., organizational hierarchy)

WITH RECURSIVE employee_hierarchy AS (
-- Base case
SELECT id, name, manager_id, 1 as level
FROM employees
WHERE manager_id IS NULL

UNION ALL

-- Recursive case
SELECT [Link], [Link], e.manager_id, [Link] + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = [Link]
)
SELECT * FROM employee_hierarchy;

Window Functions

sql
 -- Row number
SELECT
username,
age,
ROW_NUMBER() OVER (ORDER BY age DESC) as row_num
FROM users;

-- Rank
SELECT
username,
age,
RANK() OVER (ORDER BY age DESC) as rank
FROM users;

-- Partition by
SELECT
username,
department,
salary,
AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

-- Running total
SELECT
order_date,
total,
SUM(total) OVER (ORDER BY order_date) as running_total
FROM orders;

-- Lead and Lag

SELECT
order_date,
total,
LAG(total) OVER (ORDER BY order_date) as prev_total,
LEAD(total) OVER (ORDER BY order_date) as next_total
FROM orders;

CASE Statements

sql
 -- Simple CASE
SELECT
username,
age,
CASE
WHEN age < 18 THEN 'Minor'
WHEN age BETWEEN 18 AND 65 THEN 'Adult'
ELSE 'Senior'
END as age_group
FROM users;

-- CASE in aggregation
SELECT
COUNT(CASE WHEN age < 18 THEN 1 END) as minors,
COUNT(CASE WHEN age >= 18 THEN 1 END) as adults
FROM users;

COALESCE and NULLIF

sql

-- COALESCE: Return first non-null value

SELECT username, COALESCE(phone, email, 'No contact') as contact
FROM users;

-- NULLIF: Return NULL if values are equal

SELECT NULLIF(count, 0) FROM stats; -- Returns NULL instead of 0

Array Operations

sql
 -- Create array column
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
tag_list TEXT[]
);

-- Insert array
INSERT INTO tags (tag_list) VALUES (ARRAY['postgresql', 'database', 'sql']);
INSERT INTO tags (tag_list) VALUES ('{"tag1", "tag2", "tag3"}');

-- Query arrays
SELECT * FROM tags WHERE 'postgresql' = ANY(tag_list);
SELECT * FROM tags WHERE tag_list @> ARRAY['postgresql'];

-- Array functions
SELECT array_length(tag_list, 1) FROM tags;
SELECT unnest(tag_list) FROM tags; -- Expand array to rows

JSON/JSONB Operations

sql
 -- Create table with JSONB
CREATE TABLE products (
id SERIAL PRIMARY KEY,
data JSONB
);

-- Insert JSON
INSERT INTO products (data) VALUES
('{"name": "Laptop", "price": 999, "specs": {"ram": "16GB", "cpu": "i7"}}');

-- Query JSON
SELECT data->>'name' as name FROM products;
SELECT data->'specs'->>'ram' as ram FROM products;

-- JSON operators
-- -> returns JSON
-- ->> returns text
-- @> contains
-- ? key exists

-- Examples
SELECT * FROM products WHERE data->>'price' > '500';
SELECT * FROM products WHERE data @> '{"name": "Laptop"}';
SELECT * FROM products WHERE data ? 'specs';

-- Update JSON
UPDATE products
SET data = jsonb_set(data, '{price}', '899')
WHERE id = 1;

Full-Text Search

sql
 -- Create tsvector column
ALTER TABLE articles ADD COLUMN tsv tsvector;

-- Update tsvector
UPDATE articles
SET tsv = to_tsvector('english', title || ' ' || content);

-- Create GIN index

CREATE INDEX idx_articles_tsv ON articles USING gin(tsv);

-- Search
SELECT * FROM articles
WHERE tsv @@ to_tsquery('english', 'postgresql & database');

-- Ranking
SELECT title, ts_rank(tsv, query) as rank
FROM articles, to_tsquery('postgresql & database') query
WHERE tsv @@ query
ORDER BY rank DESC;

Performance & Optimization

EXPLAIN and EXPLAIN ANALYZE

sql

-- Show query plan

EXPLAIN SELECT * FROM users WHERE age > 25;

-- Show actual execution

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

-- More detailed output

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM users WHERE age > 25;

Vacuum and Analyze

sql
 -- Regular vacuum (reclaim space)
VACUUM users;

-- Full vacuum (more thorough, locks table)

VACUUM FULL users;

-- Update statistics
ANALYZE users;

-- Both together
VACUUM ANALYZE users;

-- Autovacuum is enabled by default in PostgreSQL

Query Optimization Tips

sql

-- Use indexes on WHERE, JOIN, ORDER BY columns

CREATE INDEX idx_users_age ON users(age);

-- Use LIMIT when appropriate

SELECT * FROM users ORDER BY created_at DESC LIMIT 100;

-- Avoid SELECT *
SELECT id, username, email FROM users; -- Better

-- Use EXISTS instead of COUNT

SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = [Link]);

-- Use UNION ALL instead of UNION when duplicates are okay

SELECT username FROM active_users
UNION ALL
SELECT username FROM inactive_users;

Connection Pooling

sql

-- Use connection pooling tools like:

-- - PgBouncer
-- - pgpool-II
-- For better connection management in production
Partitioning

sql

-- Range partitioning
CREATE TABLE orders (
id SERIAL,
order_date DATE,
total DECIMAL
) PARTITION BY RANGE (order_date);

-- Create partitions
CREATE TABLE orders_2024_q1 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders

FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

Quick Reference Commands

Database Management

sql

CREATE DATABASE dbname;

DROP DATABASE dbname;
\c dbname -- Connect to database
\l -- List databases

Table Management

sql

CREATE TABLE tablename (...);

DROP TABLE tablename;
ALTER TABLE tablename ...;
\dt -- List tables
\d tablename -- Describe table

User Management

sql
 CREATE USER username WITH PASSWORD 'password';
ALTER USER username WITH SUPERUSER;
DROP USER username;
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
GRANT SELECT, INSERT ON tablename TO username;
REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;
\du -- List users

Backup and Restore

bash

# Backup
pg_dump dbname > [Link]
pg_dump -U username -h hostname dbname > [Link]

# Backup specific table

pg_dump -t tablename dbname > table_backup.sql

# Restore
psql dbname < [Link]
psql -U username -h hostname dbname < [Link]

Best Practices
1. Always use transactions for multiple related operations

2. Create indexes on frequently queried columns

3. Use appropriate data types (don't use TEXT for everything)

4. Normalize your database (avoid data duplication)

5. Use constraints to maintain data integrity

6. Regular backups are essential

7. Monitor performance with EXPLAIN ANALYZE

8. Use connection pooling in production

9. Keep PostgreSQL updated for security and performance

10. Use prepared statements to prevent SQL injection

Common Patterns

Pagination

sql

SELECT * FROM users

ORDER BY id
LIMIT 10 OFFSET 20;

Upsert (INSERT or UPDATE)

sql

INSERT INTO users (id, username, email)

VALUES (1, 'john', 'john@[Link]')
ON CONFLICT (id)
DO UPDATE SET
username = [Link],
email = [Link];

Soft Delete

sql

-- Add deleted_at column

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;

-- "Delete" record
UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = 1;

-- Query non-deleted records

SELECT * FROM users WHERE deleted_at IS NULL;

Generate Series

sql
 -- Generate numbers 1-10
SELECT * FROM generate_series(1, 10);

-- Generate dates
SELECT * FROM generate_series(
'2024-01-01'::timestamp,
'2024-12-31'::timestamp,
'1 day'::interval
);

This guide covers the essentials and many advanced features of PostgreSQL. Practice these concepts with real
databases to solidify your understanding!