---
title: "PostgreSQL"
description: "Comprehensive PostgreSQL reference guide covering psql commands, database creation, tables, queries, functions, joins, transactions, indexes, and advanced SQL operations."
author: "Mohammad Abu Mattar"
canonical: https://mkabumattar.com/cheatsheets/postgresql
---

# PostgreSQL

Comprehensive PostgreSQL reference guide covering psql commands, database creation, tables, queries, functions, joins, transactions, indexes, and advanced SQL operations.

## Getting Started

### psql Connection and Basic Commands

Connect to PostgreSQL database server and execute basic commands

**Keywords:** psql, connection, connection string, host, port, database

#### Connect to Local PostgreSQL Server

```bash
psql -U postgres -h localhost -p 5432
```

_exec_
```text
psql (14.2)
Type "help" for help.
postgres=#
```

Connect to PostgreSQL server running on localhost as the postgres user

- Default port is 5432
- -U specifies username
- -h specifies hostname
- -p specifies port number

#### Connect to Specific Database

```bash
psql -U username -d database_name -h localhost
```

_exec_
```text
psql (14.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384)
Type "help" for help.
database_name=#
```

Connect directly to a specific database with credentials

- -d specifies the database name
- SSL connection shown if available

#### Connect Using Connection String

```bash
psql "postgresql://user:password@localhost:5432/mydb"
```

_exec_
```text
psql (14.2)
Type "help" for help.
mydb=#
```

Use connection string URI format to connect to database

- Format is postgresql://[user[:password]@][host][:port][/dbname]
- Secure method for credentials

**Best practices:**

- Use environment variable PGPASSWORD for passwords instead of command line
- Always specify explicit host and port in scripts
- Use .pgpass file for persistent credentials with 0600 permissions
- Never commit passwords in connection strings to version control

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined
- **undefined**: undefined

### psql Help and Meta-Commands

Navigate help system and use meta-commands in psql

**Keywords:** help, meta-commands, backslash commands, describe, list

#### Display All Meta-Commands

```sql
\?
```

_exec_
```text
General
  \copyright             show PostgreSQL usage and distribution terms
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gset [PREFIX]         execute query and store results in psql variables
  \gx                    as \g, but forces expanded output mode
  \q                     quit psql
```

Display all available meta-commands and shortcuts

- Meta-commands start with backslash
- Command list is very long, use with pager

#### Get Help on SQL Commands

```sql
\h SELECT
```

_exec_
```text
Command:     SELECT
Description: retrieve rows from a table or view
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
```

Show syntax and description for SQL commands

- Works with all SQL keywords
- Very useful for quick syntax lookup

#### List All Databases

```sql
\l
```

_exec_
```text
List of databases
Name    |  Owner   | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-------------------
postgres  | postgres | UTF8     | C       | C     |
template0 | postgres | UTF8     | C       | C     | =c/postgres
template1 | postgres | UTF8     | C       | C     | =c/postgres
testdb    | postgres | UTF8     | C       | C     |
(4 rows)
```

List all databases in the PostgreSQL server

- Shows database name, owner, encoding, and privileges
- Template databases are system templates

#### Describe Table Structure

```sql
\d users
```

_exec_
```text
Table "public.users"
Column   |       Type        | Collation | Nullable | Default
-----------+-------------------+-----------+----------+---------
id        | integer           |           | not null |
username  | character varying |           | not null |
email     | character varying |           | not null |
created_at| timestamp without |           | not null | now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE, btree (email)
```

Show detailed structure of a table including columns and constraints

- Use \d+ for extended information with access privileges
- Shows ALL constraints and indexes

**Best practices:**

- Use \d when exploring unfamiliar databases
- Save frequently used queries to .sql files instead of retyping
- Enable \timing to monitor query performance
- Use \watch to repeatedly execute and monitor queries

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

### Connection Environment Variables

Use environment variables to configure PostgreSQL connections

**Keywords:** PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE

#### Set Connection Environment Variables

```bash
export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=mydb
psql
```

_exec_
```text
psql (14.2)
Type "help" for help.
mydb=#
```

Set environment variables so psql uses them automatically

- psql reads these variables if not specified on command line
- Command line arguments override environment variables

#### Using .pgpass for Password Storage

```bash
cat ~/.pgpass
echo "localhost:5432:*:postgres:password123" >> ~/.pgpass
chmod 0600 ~/.pgpass
```

_exec_
```text
localhost:5432:*:postgres:password123
```

Store credentials in .pgpass file for passwordless connections

- File should contain lines in format: hostname:port:database:user:password
- Must have 0600 permissions for security
- Asterisk (*) matches any database

**Best practices:**

- Always use .pgpass instead of PGPASSWORD for production
- Set restrictive file permissions on .pgpass (600)
- Use different credentials for different environments
- Never commit .pgpass to version control

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

## Database Management

### CREATE DATABASE

Create new databases on PostgreSQL server

**Keywords:** CREATE DATABASE, encoding, owner, template, tablespace

#### Create Simple Database

```sql
CREATE DATABASE my_app;
```

_exec_
```text
CREATE DATABASE
```

Create a new empty database with default settings

- Default encoding is UTF8 on modern PostgreSQL
- Default owner is the current user

#### Create Database with Specifications

```sql
CREATE DATABASE company_db
  OWNER postgres
  ENCODING 'UTF8'
  LOCALE 'en_US.UTF-8'
  TEMPLATE template0;
```

_exec_
```text
CREATE DATABASE
```

Create database with specific owner, encoding, and locale settings

- template0 is clean template without any extra objects
- template1 is the default but may contain custom objects
- Always specify OWNER for clarity in production

#### Create Database with Connection Limit

```sql
CREATE DATABASE test_db
  CONNECTION LIMIT 50;
```

_exec_
```text
CREATE DATABASE
```

Create database with maximum connection limit set

- Prevents resource exhaustion from excessive connections
- Value of -1 allows unlimited connections

**Best practices:**

- Always specify OWNER explicitly for clarity
- Use UTF8 encoding for international support
- Set CONNECTION LIMIT to prevent resource exhaustion
- Use descriptive database names in snake_case
- Create from template0 for clean databases

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

### ALTER DATABASE

Modify database properties and configurations

**Keywords:** ALTER DATABASE, RENAME, OWNER, SET, RESET

#### Rename Database

```sql
ALTER DATABASE old_db RENAME TO new_db;
```

_exec_
```text
ALTER DATABASE
```

Rename an existing database

- Cannot rename database while connected to it
- No connections must be active to the database

#### Change Database Owner

```sql
ALTER DATABASE my_db OWNER TO new_owner;
```

_exec_
```text
ALTER DATABASE
```

Transfer database ownership to different user

- Current owner or superuser can perform this operation

#### Set Connection Limit

```sql
ALTER DATABASE my_db CONNECTION LIMIT 100;
```

_exec_
```text
ALTER DATABASE
```

Change the maximum number of concurrent connections

- Allows adjusting limits without dropping database

**Best practices:**

- Disconnect all clients before renaming a database
- Change ownership to dedicated database user accounts
- Monitor and adjust CONNECTION LIMIT based on usage patterns

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

### DROP DATABASE

Remove databases from PostgreSQL server

**Keywords:** DROP DATABASE, IF EXISTS, WITH, FORCE

#### Drop Database

```sql
DROP DATABASE my_db;
```

_exec_
```text
DROP DATABASE
```

Remove a database and all its objects permanently

- Operation is irreversible
- No connections can be active to the database

#### Drop Database If Exists

```sql
DROP DATABASE IF EXISTS my_db;
```

_exec_
```text
DROP DATABASE
```

Drop database only if it exists, no error if it does not

- Useful for idempotent scripts
- Does not error if database doesn't exist

#### Force Drop Database with Active Connections

```sql
DROP DATABASE my_db WITH (FORCE);
```

_exec_
```text
DROP DATABASE
```

Forcefully terminate connections and drop database

- Available in PostgreSQL 13+
- Forcefully disconnects all users before dropping

**Best practices:**

- Always backup database before dropping in production
- Use IF EXISTS for safe idempotent operations
- Require explicit confirmation before dropping in scripts
- Never drop production databases without approval process

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

## Table Operations

### CREATE TABLE

Create tables with columns and constraints

**Keywords:** CREATE TABLE, PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, DEFAULT

#### Create Basic Table

```sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```

_exec_
```text
CREATE TABLE
```

Create users table with auto-incrementing primary key and constraints

- SERIAL automatically creates sequence for id
- UNIQUE constraint on email prevents duplicates
- DEFAULT CURRENT_TIMESTAMP sets creation time automatically

#### Create Table with CHECK Constraint

```sql
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  stock INT DEFAULT 0,
  CHECK (price > 0),
  CHECK (stock >= 0)
);
```

_exec_
```text
CREATE TABLE
```

Create table with CHECK constraints to validate data

- CHECK constraint ensures price is positive
- Multiple CHECK constraints allowed
- Prevents invalid data at database level

#### Create Table with Foreign Key

```sql
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  total DECIMAL(10, 2),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
```

_exec_
```text
CREATE TABLE
```

Create orders table with foreign key referencing users

- ON DELETE CASCADE removes orders when user is deleted
- ON DELETE RESTRICT would prevent deletion if child records exist
- Foreign key enforces referential integrity

#### Create Table with ENUM Type

```sql
CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered', 'cancelled');
CREATE TABLE shipments (
  id SERIAL PRIMARY KEY,
  order_id INT NOT NULL,
  status order_status DEFAULT 'pending',
  FOREIGN KEY (order_id) REFERENCES orders(id)
);
```

_exec_
```text
CREATE TYPE
CREATE TABLE
```

Create custom ENUM type and use in table constraint

- ENUM restricts column to predefined values
- More efficient than VARCHAR with CHECK
- Type is reusable across multiple tables

**Best practices:**

- Always define PRIMARY KEY for every table
- Use explicit data types (DECIMAL for money, not FLOAT)
- Add NOT NULL constraints to required columns
- Use foreign keys to maintain referential integrity
- Include created_at with DEFAULT CURRENT_TIMESTAMP

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined
- **undefined**: undefined

### Data Types

PostgreSQL data types for different column definitions

**Keywords:** INTEGER, VARCHAR, DECIMAL, TIMESTAMP, BOOLEAN, ARRAY, JSON

#### Numeric Data Types

```sql
CREATE TABLE numeric_examples (
  small_int SMALLINT,
  regular_int INTEGER,
  big_int BIGINT,
  decimal_value DECIMAL(10, 2),
  float_value FLOAT,
  serial_auto SERIAL
);
```

_exec_
```text
CREATE TABLE
```

Create table with various numeric data types

- SMALLINT: -32768 to 32767
- INTEGER: -2147483648 to 2147483647
- BIGINT: for very large numbers
- DECIMAL for exact precision (financial data)
- SERIAL creates auto-incrementing columns

#### String and Character Data Types

```sql
CREATE TABLE string_examples (
  char_col CHAR(10),
  varchar_col VARCHAR(255),
  text_col TEXT,
  name_col VARCHAR(100) NOT NULL
);
```

_exec_
```text
CREATE TABLE
```

Create table with various string data types

- CHAR is fixed-length, padded with spaces
- VARCHAR is variable-length with limit
- TEXT is variable-length with no limit
- Use VARCHAR with limit for most cases

#### Date and Time Data Types

```sql
CREATE TABLE datetime_examples (
  date_col DATE,
  time_col TIME,
  timestamp_col TIMESTAMP,
  timestamp_tz TIMESTAMP WITH TIME ZONE
);
```

_exec_
```text
CREATE TABLE
```

Create table with date and time data types

- DATE stores only date without time
- TIME stores only time without date
- TIMESTAMP stores both date and time
- TIMESTAMP WITH TIME ZONE includes timezone information

#### JSON and Array Data Types

```sql
CREATE TABLE advanced_types (
  json_data JSON,
  jsonb_data JSONB,
  tags TEXT[],
  numbers INTEGER[]
);
```

_exec_
```text
CREATE TABLE
```

Create table with JSON and array data types

- JSONB is binary JSON with better performance
- JSON stores raw text
- ARRAY types for storing lists of values
- Can query array elements directly in SQL

**Best practices:**

- Use DECIMAL for financial data, not FLOAT
- Use TIMESTAMP WITH TIME ZONE for global applications
- Use JSONB instead of JSON for better performance
- Avoid TEXT when VARCHAR with limit is more appropriate

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

### ALTER TABLE

Modify existing table structures and constraints

**Keywords:** ALTER TABLE, ADD COLUMN, DROP COLUMN, RENAME, MODIFY

#### Add Column to Existing Table

```sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
```

_exec_
```text
ALTER TABLE
```

Add new column to existing table

- New column will be NULL for all existing rows
- Can specify DEFAULT for existing rows

#### Add Column with Default Value

```sql
ALTER TABLE users
  ADD COLUMN is_active BOOLEAN DEFAULT true;
```

_exec_
```text
ALTER TABLE
```

Add column with default value for existing and future rows

- DEFAULT applies to new inserts and existing rows

#### Rename Table and Column

```sql
ALTER TABLE users RENAME TO customer_users;
ALTER TABLE customer_users RENAME COLUMN phone TO phone_number;
```

_exec_
```text
ALTER TABLE
ALTER TABLE
```

Rename both table and column names

- Rename operations are safe, referential integrity maintained
- Consider impact on dependent views and applications

**Best practices:**

- Design tables carefully before creation to minimize ALTER operations
- Add NOT NULL columns only with DEFAULT values
- Use ALTER TABLE ... SET NOT NULL cautiously on large tables
- Test ALTER TABLE changes on development database first

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

## INSERT & UPDATE

### INSERT Statements

Insert data into tables with various methods

**Keywords:** INSERT INTO, VALUES, SELECT, DEFAULT, RETURNING

#### Insert Single Row

```sql
INSERT INTO users (username, email)
VALUES ('john_doe', 'john@example.com');
```

_exec_
```text
INSERT 0 1
```

Insert a single row with specified columns

- Columns not specified will use DEFAULT or NULL
- INSERT 0 1 means 1 row inserted with OID 0

#### Insert Multiple Rows

```sql
INSERT INTO users (username, email) VALUES
  ('alice', 'alice@example.com'),
  ('bob', 'bob@example.com'),
  ('charlie', 'charlie@example.com');
```

_exec_
```text
INSERT 0 3
```

Insert multiple rows in single statement

- Much more efficient than separate INSERT statements
- All rows inserted atomically or none

#### Insert with RETURNING Clause

```sql
INSERT INTO users (username, email)
VALUES ('diana', 'diana@example.com')
RETURNING id, username, email;
```

_exec_
```text
id | username |      email
----+----------+------------------
42 | diana    | diana@example.com
(1 row)
```

Insert row and return generated values immediately

- RETURNING is PostgreSQL extension
- Useful for getting auto-generated IDs
- Can return any columns or expressions

#### Insert from SELECT Query

```sql
INSERT INTO users_backup (username, email)
SELECT username, email FROM users WHERE created_at > CURRENT_DATE - INTERVAL '30 days';
```

_exec_
```text
INSERT 0 15
```

Insert rows from results of SELECT query

- Useful for copying or archiving data
- Can filter and transform data during copy

**Best practices:**

- Use multi-row INSERT for better performance
- Always specify columns explicitly for clarity
- Use RETURNING to confirm data was inserted correctly
- Validate data before insertion
- Use transactions for multiple related inserts

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined
- **undefined**: undefined

### UPDATE Statements

Modify existing data in tables

**Keywords:** UPDATE, SET, WHERE, RETURNING, FROM

#### Update Single Column

```sql
UPDATE users
SET email = 'newemail@example.com'
WHERE username = 'john_doe';
```

_exec_
```text
UPDATE 1
```

Update specific column for matching rows

- WHERE clause is critical to avoid updating all rows
- UPDATE 1 means 1 row was updated

#### Update Multiple Columns

```sql
UPDATE users
SET
  email = 'john.doe@example.com',
  is_active = true,
  updated_at = CURRENT_TIMESTAMP
WHERE id = 1;
```

_exec_
```text
UPDATE 1
```

Update multiple columns in single statement

- Can update independent columns simultaneously
- Use CURRENT_TIMESTAMP for automatic update tracking

#### Update with Expression

```sql
UPDATE products
SET price = price * 1.10, updated_at = CURRENT_TIMESTAMP
WHERE category = 'electronics';
```

_exec_
```text
UPDATE 15
```

Update columns using expressions referencing current values

- price * 1.10 increases price by 10 percent
- Updated 15 products with category electronics

#### Update with RETURNING

```sql
UPDATE users
SET last_login = CURRENT_TIMESTAMP
WHERE username = 'alice'
RETURNING id, username, last_login;
```

_exec_
```text
id | username |     last_login
----+----------+---------------------
2 | alice    | 2026-02-28 14:30:45
(1 row)
```

Update and return the modified row data

- Confirms what was actually updated
- Returns new values after the update

**Best practices:**

- Always include WHERE clause to target specific rows
- Use RETURNING to verify changes were applied
- Consider adding updated_at timestamp column
- Test UPDATE on development database first
- Use transactions to ensure consistency

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

### DELETE Statements

Remove data from tables

**Keywords:** DELETE, WHERE, RETURNING, ON DELETE CASCADE

#### Delete Rows with Condition

```sql
DELETE FROM users WHERE id = 42;
```

_exec_
```text
DELETE 1
```

Delete specific row matching condition

- DELETE 1 means 1 row was deleted
- WHERE clause is critical

#### Delete Multiple Rows

```sql
DELETE FROM orders
WHERE status = 'cancelled'
AND created_at < CURRENT_DATE - INTERVAL '90 days'
RETURNING id, order_date;
```

_exec_
```text
id |  order_date
----+---------------------
5 | 2025-11-15 10:20:30
12 | 2025-10-20 14:45:20
(2 rows)
```

Delete old cancelled orders and return their details

- Complex WHERE allows deleting old data selectively
- RETURNING shows what was deleted before deletion

#### Delete All Rows

```sql
DELETE FROM log_entries;
```

_exec_
```text
DELETE 1000
```

Delete all rows from table (no WHERE clause)

- Very dangerous operation
- Use TRUNCATE for faster deletion of all rows

**Best practices:**

- Always backup table before bulk DELETE operations
- Use WHERE clause to limit scope
- Use RETURNING to verify deleted data
- Prefer TRUNCATE for deleting all rows
- Use soft deletes (is_deleted column) for important data

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

## SELECT Basics

### SELECT and WHERE

Retrieve data with conditions and filtering

**Keywords:** SELECT, WHERE, AND, OR, NOT, IN, BETWEEN

#### Select All Columns

```sql
SELECT * FROM users;
```

_exec_
```text
id | username |        email        | created_at
----+----------+----------------------+---------------------
1 | john_doe | john@example.com   | 2026-01-15 10:30:00
2 | alice    | alice@example.com  | 2026-01-20 14:25:00
3 | bob      | bob@example.com    | 2026-02-01 09:15:00
(3 rows)
```

Retrieve all columns from all rows in users table

- Using * is fine for exploration but avoid in production
- Specify columns explicitly for performance

#### Select Specific Columns

```sql
SELECT id, username, email FROM users;
```

_exec_
```text
id | username |        email
----+----------+----------------------
1 | john_doe | john@example.com
2 | alice    | alice@example.com
3 | bob      | bob@example.com
(3 rows)
```

Select only specific columns from table

- More efficient than SELECT *
- Only fetches needed columns

#### SELECT with WHERE Condition

```sql
SELECT * FROM users WHERE username = 'alice';
```

_exec_
```text
id | username |       email       |     created_at
----+----------+-------------------+---------------------
2 | alice    | alice@example.com | 2026-01-20 14:25:00
(1 row)
```

Filter results using WHERE clause

- String values must be in single quotes
- Uses index on username for faster lookup

#### WHERE with Multiple Conditions

```sql
SELECT * FROM users
WHERE created_at > '2026-01-01'
  AND is_active = true;
```

_exec_
```text
id | username |        email       |     created_at
----+----------+--------------------+---------------------
1 | john_doe | john@example.com  | 2026-01-15 10:30:00
2 | alice    | alice@example.com | 2026-01-20 14:25:00
(2 rows)
```

Filter with AND operator for multiple conditions

- Both conditions must be true for row to match
- Can use OR for alternative conditions

#### WHERE with IN Operator

```sql
SELECT * FROM orders
WHERE status IN ('pending', 'shipped', 'processing');
```

_exec_
```text
id | user_id | status     |   order_date
----+---------+------------+---------------------
1 |       1 | pending    | 2026-02-20 11:00:00
3 |       2 | shipped    | 2026-02-18 15:30:00
5 |       3 | processing | 2026-02-25 08:45:00
(3 rows)
```

Filter for rows matching any value in list

- IN is more readable than multiple OR conditions
- Can use subquery with IN operator

#### WHERE with BETWEEN

```sql
SELECT * FROM products
WHERE price BETWEEN 10.00 AND 100.00;
```

_exec_
```text
id | name           | price
----+----------------+--------
2 | Keyboard       | 45.99
4 | Monitor        | 89.50
(2 rows)
```

Filter for values within range (inclusive)

- BETWEEN is inclusive of both boundaries
- Works with numbers, dates, and strings

**Best practices:**

- Always use WHERE to filter data, don't fetch unnecessary rows
- Create indexes on frequently filtered columns
- Use IN instead of multiple OR conditions
- Be careful with data types in comparisons

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

### ORDER BY and LIMIT

Sort and limit query results

**Keywords:** ORDER BY, ASC, DESC, LIMIT, OFFSET

#### Order Results Ascending

```sql
SELECT username, created_at FROM users
ORDER BY created_at ASC;
```

_exec_
```text
username |     created_at
----------+---------------------
john_doe | 2026-01-15 10:30:00
alice    | 2026-01-20 14:25:00
bob      | 2026-02-01 09:15:00
(3 rows)
```

Sort results by created_at in ascending order (oldest first)

- ASC is the default, can be omitted
- Sorts from lowest to highest value

#### Order Results Descending

```sql
SELECT username, created_at FROM users
ORDER BY created_at DESC;
```

_exec_
```text
username |     created_at
----------+---------------------
bob      | 2026-02-01 09:15:00
alice    | 2026-01-20 14:25:00
john_doe | 2026-01-15 10:30:00
(3 rows)
```

Sort results by created_at in descending order (newest first)

- DESC sorts from highest to lowest value
- Useful for getting recent records

#### Limit Results to First N Rows

```sql
SELECT * FROM products
ORDER BY price DESC
LIMIT 5;
```

_exec_
```text
id | name               | price
----+--------------------+--------
1 | Premium Laptop     |899.99
2 | Desktop Computer   |749.99
3 | Tablet             |399.99
4 | Monitor            | 89.50
5 | Keyboard           | 45.99
(5 rows)
```

Order by price descending and return top 5 most expensive products

- LIMIT restricts number of rows returned
- Improves performance for large result sets

#### Pagination with OFFSET and LIMIT

```sql
SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 20;
```

_exec_
```text
id | username |        email        |     created_at
----+----------+----------------------+---------------------
21 | user21   | user21@example.com  | 2026-01-15 10:30:00
22 | user22   | user22@example.com  | 2026-01-20 14:25:00
(10 rows)
```

Skip first 20 rows and return next 10 (pagination)

- OFFSET skips specified number of rows
- Page 3 with page size 10 = LIMIT 10 OFFSET 20
- Use ORDER BY for consistent pagination

**Best practices:**

- Always use ORDER BY when results order matters
- Use LIMIT to prevent returning huge result sets
- Create indexes on ORDER BY columns for performance
- Use OFFSET with LIMIT for pagination
- Consider keyset pagination for large datasets

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

## JOINs & Subqueries

### JOIN Types

Combine data from multiple tables using different join types

**Keywords:** INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN

#### INNER JOIN Two Tables

```sql
SELECT users.username, orders.id, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;
```

_exec_
```text
username | id | total
----------+----+--------
john_doe |  1 |100.00
alice    |  2 | 50.00
bob      |  3 | 75.00
(3 rows)
```

Return only rows where both tables have matching records

- Only returns matching rows from both tables
- ON clause specifies the join condition
- INNER is the default join type

#### LEFT JOIN Preserving All Left Rows

```sql
SELECT users.username, COUNT(orders.id) as order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.username;
```

_exec_
```text
username | order_count
----------+-------------
john_doe |           2
alice    |           1
bob      |           0
(3 rows)
```

Include all users even if they have no orders

- bob appears with 0 orders because of LEFT JOIN
- INNER JOIN would omit users with no orders
- Useful for finding missing related records

#### RIGHT JOIN

```sql
SELECT users.username, orders.id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
```

_exec_
```text
username | id
----------+----
john_doe |  1
alice    |  2
bob      |  3
         |  4
(4 rows)
```

Include all orders even if user was deleted

- Shows order 4 with NULL username (orphaned order)
- Opposite of LEFT JOIN

#### FULL OUTER JOIN

```sql
SELECT users.username, orders.id
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;
```

_exec_
```text
username | id
----------+----
john_doe |  1
alice    |  2
bob      |  3
         |  4
(4 rows)
```

Include rows from both tables even if no match

- Combines LEFT and RIGHT JOIN behavior
- Shows unmatched rows from both sides as NULL

#### Join with Multiple Tables

```sql
SELECT users.username, orders.id, products.name
FROM users
INNER JOIN orders ON users.id = orders.user_id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id;
```

_exec_
```text
username | id | name
----------+----+-----
john_doe |  1 | Laptop
john_doe |  1 | Mouse
alice    |  2 | Keyboard
(3 rows)
```

Chain multiple JOINs to combine data from 4 tables

- Each JOIN adds more conditions
- Order of JOINs can affect performance

**Best practices:**

- Use explicit JOIN keywords (INNER, LEFT, RIGHT) for clarity
- Always use ON clause with clear join conditions
- Create indexes on join columns for performance
- Test complex joins on sample data first
- Consider using aliases for readability

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined
- **undefined**: undefined

### Subqueries

Use queries within queries for complex data retrieval

**Keywords:** subquery, nested query, WHERE subquery, FROM subquery, scalar subquery

#### Subquery in WHERE Clause

```sql
SELECT username, email FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);
```

_exec_
```text
username |        email
----------+----------------------
john_doe | john@example.com
alice    | alice@example.com
(2 rows)
```

Find users who placed orders totaling more than 100

- Subquery returns list of user_ids for IN clause
- Subquery executes first, result used by outer query

#### Subquery in FROM Clause

```sql
SELECT avg_order FROM (
  SELECT AVG(total) as avg_order FROM orders
) as order_stats;
```

_exec_
```text
avg_order
-----------
75.00
(1 row)
```

Treat subquery result as a table in FROM clause

- Subquery must have alias (order_stats)
- Useful for complex aggregations

#### Scalar Subquery in SELECT

```sql
SELECT username,
  (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) as order_count
FROM users;
```

_exec_
```text
username | order_count
----------+-------------
john_doe |           2
alice    |           1
bob      |           0
(3 rows)
```

Use subquery in SELECT to get count for each user

- Scalar subquery must return single value per row
- Uses correlated subquery (references outer table)
- Can be performance intensive on large datasets

**Best practices:**

- Prefer JOINs over subqueries when possible for performance
- Use CTEs (WITH) instead of nested subqueries for readability
- Ensure subquery returns expected number of rows
- Avoid correlated subqueries in SELECT clause

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

## Aggregation & Grouping

### Aggregate Functions

Use functions that operate on multiple rows

**Keywords:** COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT

#### COUNT Aggregation

```sql
SELECT COUNT(*) as total_users FROM users;
```

_exec_
```text
total_users
-------------
          3
(1 row)
```

Count total number of rows in table

- COUNT(*) counts all rows including NULLs
- COUNT(column) counts non-NULL values in column

#### SUM Aggregation

```sql
SELECT SUM(total) as total_revenue FROM orders;
```

_exec_
```text
total_revenue
---------------
      225.00
(1 row)
```

Sum all order totals to get total revenue

- SUM ignores NULL values
- Returns NULL if no rows selected

#### Average and Min/Max

```sql
SELECT
  AVG(price) as avg_price,
  MIN(price) as min_price,
  MAX(price) as max_price
FROM products;
```

_exec_
```text
avg_price | min_price | max_price
-----------+-----------+-----------
    267.37 |      9.99 |    899.99
(1 row)
```

Calculate average, minimum, and maximum product prices

- AVG ignores NULL values
- MIN and MAX work with any comparable data type

#### Multiple Aggregations

```sql
SELECT
  COUNT(*) as order_count,
  COUNT(DISTINCT user_id) as unique_users,
  SUM(total) as total_sales,
  AVG(total) as avg_order_value
FROM orders;
```

_exec_
```text
order_count | unique_users | total_sales | avg_order_value
-------------+--------------+-------------+-----------------
          8 |            3 |      600.00 |          75.00
(1 row)
```

Calculate multiple aggregate metrics

- DISTINCT ensures counting unique values only
- Multiple aggregates in single query is efficient

**Best practices:**

- Understand NULL behavior in aggregate functions
- Use DISTINCT within aggregates when needed
- Create indexes on columns used in aggregates
- Be cautious with AVG on large datasets with outliers

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

### GROUP BY and HAVING

Group rows and filter aggregated results

**Keywords:** GROUP BY, HAVING, aggregate functions, grouping sets

#### GROUP BY Single Column

```sql
SELECT user_id, COUNT(*) as order_count, SUM(total) as user_total
FROM orders
GROUP BY user_id;
```

_exec_
```text
user_id | order_count | user_total
---------+-------------+------------
      1 |           2 |     150.00
      2 |           1 |      50.00
      3 |           3 |     200.00
(3 rows)
```

Group orders by user and calculate stats per user

- GROUP BY organizes rows into groups
- Aggregates calculate values for each group

#### GROUP BY Multiple Columns

```sql
SELECT DATE(order_date) as order_day, status, COUNT(*) as count
FROM orders
GROUP BY DATE(order_date), status;
```

_exec_
```text
order_day  | status | count
-----------+---------+-------
2026-02-20 | pending |     2
2026-02-20 | shipped |     1
2026-02-25 | delivered |   1
(3 rows)
```

Group by date and status to see distribution

- Results grouped by both columns in GROUP BY
- Multiple grouping columns refine the grouping

#### HAVING Clause Filter

```sql
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 1;
```

_exec_
```text
user_id | order_count
---------+-------------
      1 |           2
      3 |           3
(2 rows)
```

Find users with more than 1 order using HAVING

- HAVING filters on aggregate values (use after GROUP BY)
- WHERE filters on individual rows (use before GROUP BY)
- User 2 excluded because they have only 1 order

#### WHERE and HAVING Together

```sql
SELECT category, SUM(price) as total_price, COUNT(*) as item_count
FROM products
WHERE status = 'active'
GROUP BY category
HAVING SUM(price) > 500;
```

_exec_
```text
category  | total_price | item_count
----------+-------------+------------
electronics|    1250.00 |          5
furniture |     750.00 |          3
(2 rows)
```

Filter active products, group by category, show high-value categories

- WHERE applies before grouping (filters rows)
- HAVING applies after grouping (filters groups)

**Best practices:**

- All non-aggregated columns must be in GROUP BY
- Use WHERE for row filtering before GROUP BY
- Use HAVING for aggregate filtering after GROUP BY
- Create indexes on GROUP BY columns
- Remember aggregate functions return single value per group

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

## Advanced Queries

### Window Functions

Perform calculations across rows without grouping

**Keywords:** OVER, ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, window partition

#### ROW_NUMBER for Ranking

```sql
SELECT
  username,
  order_total,
  ROW_NUMBER() OVER (ORDER BY order_total DESC) as rank
FROM user_orders;
```

_exec_
```text
username | order_total | rank
----------+-------------+------
alice    |      300.00 |    1
bob      |      250.00 |    2
john_doe |      200.00 |    3
(3 rows)
```

Assign unique rank to each row based on order total

- ROW_NUMBER always gives unique sequential numbers
- Rows with same value get different row numbers

#### RANK with Ties

```sql
SELECT
  username,
  salary,
  RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees;
```

_exec_
```text
username | salary | salary_rank
----------+--------+-------------
alice    |  80000 |           1
bob      |  80000 |           1
charlie  |  75000 |           3
(3 rows)
```

Rank with tied rows sharing same rank

- RANK skips numbers after ties (1, 1, 3)
- DENSE_RANK does not skip (1, 1, 2)

#### Partition Over Window

```sql
SELECT
  department,
  username,
  salary,
  AVG(salary) OVER (PARTITION BY department) as avg_dept_salary
FROM employees;
```

_exec_
```text
department | username | salary | avg_dept_salary
-----------+----------+--------+-----------------
sales      | alice    |  80000 |           75000
sales      | bob      |  70000 |           75000
it         | charlie  |  90000 |           90000
(3 rows)
```

Calculate average salary per department as window function

- PARTITION BY divides rows into separate windows
- Aggregate calculated within each partition

#### LAG and LEAD for Sequential Access

```sql
SELECT
  order_date,
  total,
  LAG(total) OVER (ORDER BY order_date) as prev_order,
  LEAD(total) OVER (ORDER BY order_date) as next_order
FROM orders;
```

_exec_
```text
order_date  | total | prev_order | next_order
-----------+-------+------------+----------
2026-02-20 |100.00 |            |     150.00
2026-02-22 |150.00|     100.00 |      75.00
2026-02-25 | 75.00|     150.00 |
(3 rows)
```

Get previous and next order amounts for each order

- LAG accesses previous row value
- LEAD accesses next row value
- NULL for first/last rows without previous/next

**Best practices:**

- Use window functions to avoid correlated subqueries
- Create indexes on columns in ORDER BY of window function
- PARTITION BY reduces processing, use when applicable
- Test window functions with small datasets first

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

### Common Table Expressions (CTEs)

Use WITH clause to define temporary result sets

**Keywords:** WITH, CTE, recursive, temporary result

#### Simple CTE

```sql
WITH high_value_orders AS (
  SELECT * FROM orders WHERE total > 100
)
SELECT username, email FROM users
WHERE id IN (SELECT user_id FROM high_value_orders);
```

_exec_
```text
username |        email
----------+----------------------
john_doe | john@example.com
alice    | alice@example.com
(2 rows)
```

Use CTE to define high-value orders, then find their customers

- CTE defined in WITH clause, then used in main query
- More readable than nested subqueries
- CTE scoped to single query only

#### Multiple CTEs

```sql
WITH user_order_counts AS (
  SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id
),
active_users AS (
  SELECT id FROM users WHERE is_active = true
)
SELECT u.id, u.username, oc.order_count
FROM active_users u
JOIN user_order_counts oc ON u.id = oc.user_id;
```

_exec_
```text
id | username | order_count
----+----------+-------------
  1 | john_doe |           2
  2 | alice    |           1
(2 rows)
```

Define multiple CTEs and use them together

- Each CTE separated by comma
- CTEs reference each other

#### Recursive CTE

```sql
WITH RECURSIVE numbers AS (
  SELECT 1 as n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT * FROM numbers;
```

_exec_
```text
n
---
  1
  2
  3
  4
  5
(5 rows)
```

Use recursive CTE to generate sequence of numbers

- Recursive CTE has initial query and recursive part
- Useful for hierarchical or tree data
- Must have termination condition to prevent infinite loop

**Best practices:**

- Use CTEs for readability instead of deeply nested subqueries
- Name CTEs descriptively
- Recursive CTEs need clear termination conditions
- Test recursive CTEs with limit to avoid infinite loops

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

### Indexes and Views

Create indexes for performance and views for convenience

**Keywords:** CREATE INDEX, CREATE VIEW, index types, materialized view

#### Create Index on Column

```sql
CREATE INDEX idx_users_email ON users(email);
```

_exec_
```text
CREATE INDEX
```

Create index on email column for faster lookups

- Greatly improves SELECT performance on indexed column
- Increases INSERT/UPDATE time slightly
- Should be created on frequently queried columns

#### Create Composite Index

```sql
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date DESC);
```

_exec_
```text
CREATE INDEX
```

Create index on multiple columns for complex queries

- Useful for queries filtering and sorting by these columns
- Column order matters for query optimization

#### Create View

```sql
CREATE VIEW user_order_summary AS
SELECT
  u.id, u.username, COUNT(o.id) as order_count, SUM(o.total) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;
```

_exec_
```text
CREATE VIEW
```

Create view for user order statistics

- View acts like a table but contains query logic
- SELECT from view returns dynamically calculated results
- Useful for common complex queries

#### Query a View

```sql
SELECT username, order_count, total_spent
FROM user_order_summary
WHERE order_count > 2;
```

_exec_
```text
username | order_count | total_spent
----------+-------------+-------------
john_doe |           3 |      400.00
(1 row)
```

Query the view like a regular table

- Views simplify complex queries
- Can be easier to maintain than repeating complex SQL

**Best practices:**

- Create indexes on columns used in WHERE and JOIN conditions
- Avoid over-indexing (indexes slow down writes)
- Use EXPLAIN ANALYZE to verify indexes help
- Create views for commonly used complex queries
- Document views with comments

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

## Database Functions

### String Functions

Manipulate and analyze text strings

**Keywords:** CONCAT, SUBSTRING, UPPER, LOWER, LENGTH, TRIM, REPLACE, SPLIT_PART

#### String Concatenation

```sql
SELECT
  CONCAT(first_name, ' ', last_name) as full_name,
  CONCAT(username, '@example.com') as email
FROM users;
```

_exec_
```text
full_name  |          email
-----------+------------------------
John Doe   | john_doe@example.com
Alice Smith| alice@example.com
(2 rows)
```

Combine multiple string columns into single value

- CONCAT returns NULL if any argument is NULL
- Alternative: use || operator

#### Substring Extraction

```sql
SELECT
  username,
  SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) as email_prefix
FROM users;
```

_exec_
```text
username | email_prefix
----------+---------------
john_doe | john
alice    | alice
(2 rows)
```

Extract portion of string using SUBSTRING function

- SUBSTRING(string FROM start FOR length)
- Useful for parsing data from formatted strings

#### Case Conversion

```sql
SELECT
  username,
  UPPER(username) as username_upper,
  LOWER(email) as email_lower
FROM users;
```

_exec_
```text
username | username_upper | email_lower
----------+----------------+--------------------
John_Doe | JOHN_DOE       | john@example.com
alice    | ALICE          | alice@example.com
(2 rows)
```

Convert strings to uppercase and lowercase

- UPPER converts to uppercase
- LOWER converts to lowercase
- Useful for case-insensitive comparisons

#### String Length and Trimming

```sql
SELECT
  username,
  LENGTH(username) as username_length,
  TRIM(notes) as cleaned_notes
FROM users;
```

_exec_
```text
username | username_length | cleaned_notes
----------+-----------------+---------------
john_doe |               8 | clean note
alice    |               5 | another note
(2 rows)
```

Get string length and remove leading/trailing whitespace

- LENGTH returns character count
- TRIM removes spaces, LTRIM from left, RTRIM from right

#### String Replacement

```sql
SELECT
  name,
  REPLACE(description, 'old_text', 'new_text') as updated_description
FROM products;
```

_exec_
```text
name    | updated_description
--------+---------------------
Laptop | new_text here
(1 rows)
```

Replace occurrences of string within text

- REPLACE(string, from, to) replaces all occurrences
- Case-sensitive

**Best practices:**

- Use LOWER for case-insensitive comparisons instead of UPPER
- Handle NULL returns from string functions
- Use TRIM to clean user input
- Consider encoding issues with international characters

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

### Date and Time Functions

Work with dates and timestamps

**Keywords:** CURRENT_DATE, CURRENT_TIMESTAMP, EXTRACT, DATE_TRUNC, INTERVAL, AGE

#### Current Date and Time

```sql
SELECT
  CURRENT_DATE as today,
  CURRENT_TIMESTAMP as now,
  NOW() as also_now;
```

_exec_
```text
today      |            now             |           also_now
-----------+----------------------------+----------------------------
2026-02-28 | 2026-02-28 14:30:45.123456 | 2026-02-28 14:30:45.123456
(1 row)
```

Get current date and timestamp

- CURRENT_DATE returns date without time
- CURRENT_TIMESTAMP returns timestamp with timezone
- NOW() is alias for CURRENT_TIMESTAMP

#### Extract Date Components

```sql
SELECT
  created_at,
  EXTRACT(YEAR FROM created_at) as year,
  EXTRACT(MONTH FROM created_at) as month,
  EXTRACT(DAY FROM created_at) as day
FROM users;
```

_exec_
```text
created_at  | year | month | day
-----------+------+-------+-----
2026-01-15 | 2026 |     1 |  15
2026-02-28 | 2026 |     2 |  28
(2 rows)
```

Extract individual components from date/timestamp

- EXTRACT returns numeric values for date parts
- Can use with YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

#### Date Arithmetic with INTERVAL

```sql
SELECT
  order_date,
  order_date + INTERVAL '7 days' as expected_delivery,
  CURRENT_DATE - INTERVAL '30 days' as thirty_days_ago
FROM orders;
```

_exec_
```text
order_date | expected_delivery | thirty_days_ago
-----------+------------------+------------------
2026-02-20 |      2026-02-27  |     2025-12-29
2026-02-25 |      2026-03-04  |     2025-12-29
(2 rows)
```

Add and subtract intervals from dates

- INTERVAL specifies duration (days, hours, months, years)
- Result type depends on operand types (date + interval = date)

#### Calculate Age Between Dates

```sql
SELECT
  username,
  created_at,
  AGE(CURRENT_TIMESTAMP, created_at) as account_age
FROM users;
```

_exec_
```text
username | created_at | account_age
----------+------------+-------------------------------
john_doe | 2026-01-15 | 1 mon 13 days 04:30:45.123456
alice    | 2026-01-20 | 1 mon 08 days 00:25:30.654321
(2 rows)
```

Calculate age/duration between two timestamps

- AGE returns interval showing years, months, days, etc
- Useful for finding how old accounts or events are

#### Date Truncation

```sql
SELECT
  order_date,
  DATE_TRUNC('day', order_date) as day_start,
  DATE_TRUNC('month', order_date) as month_start,
  DATE_TRUNC('year', order_date) as year_start
FROM orders;
```

_exec_
```text
order_date  | day_start   | month_start | year_start
-----------+------------+------------+----------
2026-02-20 | 2026-02-20 | 2026-02-01 | 2026-01-01
(1 rows)
```

Truncate timestamp to specified unit

- Useful for grouping by date parts
- Can truncate to hour, day, month, year, etc

**Best practices:**

- Always use TIMESTAMP WITH TIME ZONE for global applications
- Use CURRENT_TIMESTAMP for consistency across queries
- Use DATE_TRUNC for time-based grouping
- Remember to handle timezone awareness
- Test date calculations with edge cases (leap years, DST)

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined
- **undefined**: undefined

### Math Functions

Perform mathematical calculations

**Keywords:** ABS, ROUND, CEIL, FLOOR, POWER, SQRT, MOD

#### Basic Math Operations

```sql
SELECT
  price,
  ABS(price - 100) as distance_from_100,
  ROUND(price, 2) as rounded_price,
  FLOOR(price) as floor_price,
  CEIL(price) as ceil_price
FROM products;
```

_exec_
```text
price | distance_from_100 | rounded_price | floor_price | ceil_price
-------+-------------------+---------------+-------------+----------
89.50 |              10.50|         89.50 |          89 |         90
(1 row)
```

Use various math functions on price values

- ABS returns absolute value
- ROUND(number, decimals) rounds to specified places
- FLOOR rounds down, CEIL rounds up

#### Power and Square Root

```sql
SELECT
  quantity,
  POWER(quantity, 2) as quantity_squared,
  SQRT(quantity) as square_root
FROM inventory;
```

_exec_
```text
quantity | quantity_squared | square_root
----------+------------------+-------------
      10 |              100 |     3.16228
(1 row)
```

Calculate power and square root of values

- POWER(base, exponent) raises to power
- SQRT returns square root

#### Modulo Operation

```sql
SELECT
  id,
  MOD(id, 10) as remainder_of_div_10
FROM users
WHERE MOD(id, 2) = 0;
```

_exec_
```text
id | remainder_of_div_10
----+-------------------
  2 |                   2
  4 |                   4
  6 |                   6
(3 rows)
```

Find remainder of division and filter even IDs

- MOD(a, b) returns remainder of a/b
- MOD(id, 2) = 0 finds even numbers

**Best practices:**

- Use ROUND with appropriate decimal places for currency
- Remember NULL propagation in math operations
- Use CEIL for stock calculations
- Consider performance impact of complex math functions

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

## Transactions & Performance

### Transaction Control

Use transactions for data consistency

**Keywords:** BEGIN, COMMIT, ROLLBACK, SAVEPOINT, ACID

#### Basic Transaction

```sql
BEGIN;
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE users SET balance = balance + 100 WHERE id = 2;
COMMIT;
```

_exec_
```text
BEGIN
UPDATE 1
UPDATE 1
COMMIT
```

Transfer 100 units from user 1 to user 2 atomically

- Both updates succeed or both fail
- Ensures consistency of balance transfer

#### Transaction Rollback

```sql
BEGIN;
UPDATE products SET stock = stock - 10 WHERE id = 5;
SELECT stock FROM products WHERE id = 5;
ROLLBACK;
```

_exec_
```text
BEGIN
UPDATE 1
 stock
-------
    25
(1 row)
ROLLBACK
```

Start transaction, make changes, then rollback

- ROLLBACK discards all changes in transaction
- Stock reverts to previous value after rollback

#### Savepoint for Partial Rollback

```sql
BEGIN;
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
SAVEPOINT sp1;
INSERT INTO users (username, email) VALUES ('bob', NULL);
ROLLBACK TO sp1;
INSERT INTO users (username, email) VALUES ('charlie', 'charlie@example.com');
COMMIT;
```

_exec_
```text
BEGIN
INSERT 0 1
SAVEPOINT
ERROR: null value in column violates not-null constraint
ROLLBACK
INSERT 0 1
COMMIT
```

Use SAVEPOINT to partially rollback failed operations

- Savepoint allows rolling back to previous point
- Alice and Charlie inserted, Bob's insert rolled back

**Best practices:**

- Keep transactions as short as possible
- Avoid long-running transactions that lock resources
- Use explicit transaction boundaries for related operations
- Handle transaction rollback in application code
- Use savepoints for complex multi-step operations

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined
- **undefined**: undefined

### EXPLAIN and ANALYZE

Analyze query performance and execution plans

**Keywords:** EXPLAIN, ANALYZE, execution plan, query optimization, index usage

#### Basic EXPLAIN Plan

```sql
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
```

_exec_
```text
Seq Scan on users (cost=0.00..35.50 rows=1 width=200)
  Filter: (email = 'john@example.com'::text)
Planning Time: 0.085 ms
```

Show query execution plan without actually running query

- Seq Scan means full table scan (no index used)
- Cost is relative unit of query expense
- Rows indicates estimated number of returned rows

#### EXPLAIN with ANALYZE

```sql
EXPLAIN ANALYZE SELECT u.username, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;
```

_exec_
```text
GroupAggregate (cost=100.25..108.50 rows=3 width=20) (actual time=1.235..1.250 rows=3 loops=1)
  -> Sort (cost=100.25..100.50 rows=3 width=20) (actual time=0.845..0.850 rows=3 loops=1)
        Sort Key: u.id
        -> Hash Left Join (cost=35.50..100.00 rows=100 width=20) (actual time=0.250..0.500 rows=100 loops=1)
Planning Time: 0.125 ms
Execution Time: 1.350 ms
```

Run query and show actual execution statistics

- actual time shows real execution time
- actual rows shows actual returned rows vs estimated
- Useful for query optimization

#### EXPLAIN with VERBOSE and COSTS

```sql
EXPLAIN (ANALYZE, VERBOSE, COSTS)
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
```

_exec_
```text
Seq Scan on public.products (cost=0.00..35.50 rows=150 width=200)
  Output: id, name, price, stock
  Filter: ((price >= '10.00'::numeric) AND (price <= '100.00'::numeric))
  Planning Time: 0.050 ms
  Execution Time: 0.500 ms
```

Show detailed execution plan with output columns

- VERBOSE shows all output columns and filters
- Helps understand exactly what query is doing

**Best practices:**

- Use EXPLAIN ANALYZE to verify index effectiveness
- Look for Seq Scan on large tables (should use index)
- Check if estimated rows match actual rows
- Identify slow operations (high cost nodes)
- Use ANALYZE to gather table statistics

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined

### Query Optimization Tips

Optimize queries for better performance

**Keywords:** indexing strategy, query optimization, prepared statements, query rewriting, statistics

#### Index for Common Queries

```sql
CREATE INDEX idx_users_status_created ON users(status, created_at DESC);
SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 10;
```

_exec_
```text
CREATE INDEX
 id | username | status | created_at
----+----------+--------+---------------------------
  5 | alice    | active | 2026-02-28 14:30:00
  3 | bob      | active | 2026-02-27 10:15:00
(2 rows)
```

Create compound index on commonly filtered columns

- Index on (status, created_at) speeds up query
- Column order in index matters
- DESC in index aids ORDER BY DESC queries

#### Use VACUUM to Maintain Performance

```bash
VACUUM ANALYZE users;
VACUUM FULL;
```

_exec_
```text
VACUUM
VACUUM
```

Reclaim space and update query planner statistics

- VACUUM removes dead rows
- ANALYZE updates optimizer statistics
- VACUUM FULL locks table (use during maintenance)

#### Batch Large Updates

```sql
UPDATE products
SET stock = stock - 1
WHERE id IN (SELECT product_id FROM orders WHERE status = 'shipped' LIMIT 1000);
```

_exec_
```text
UPDATE 950
```

Update in batches to reduce lock contention

- LIMIT prevents huge updates holding locks too long
- Run multiple times to process all records

#### Prepared Statements

```bash
PREPARE get_user (INT) AS SELECT * FROM users WHERE id = $1;
EXECUTE get_user(1);
EXECUTE get_user(2);
DEALLOCATE get_user;
```

_exec_
```text
PREPARE
 id | username |         email       | created_at
----+----------+--------------------+---------------------
  1 | john_doe | john@example.com  | 2026-01-15 10:30:00
(1 row)
PREPARE
DEALLOCATE
```

Prepare and reuse statements to prevent reparsing

- Prepared statements improve performance for repeated queries
- Also prevent SQL injection attacks

**Best practices:**

- Index frequently searched columns
- Avoid indexing low-cardinality columns
- Use EXPLAIN ANALYZE to verify optimization
- Regularly ANALYZE tables for statistics
- Consider denormalization for read-heavy workloads
- Use partitioning for very large tables

**Common errors:**

- **undefined**: undefined
- **undefined**: undefined
- **undefined**: undefined