Cheatsheets

PostgreSQL

PostgreSQL

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

10 Categories 27 Sections 100 Examples

Getting Started

psql Connection and Basic Commands

Connect to PostgreSQL database server and execute basic commands

Connect to Local PostgreSQL Server

Connect to PostgreSQL server running on localhost as the postgres user

Code
Terminal window
psql -U postgres -h localhost -p 5432
Execution 
psql (14.2)
Type "help" for help.
postgres=#
  • Default port is 5432
  • -U specifies username
  • -h specifies hostname
  • -p specifies port number

Connect to Specific Database

Connect directly to a specific database with credentials

Code
Terminal window
psql -U username -d database_name -h localhost
Execution 
psql (14.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384)
Type "help" for help.
database_name=#
  • -d specifies the database name
  • SSL connection shown if available

Connect Using Connection String

Use connection string URI format to connect to database

Code
Terminal window
psql "postgresql://user:password@localhost:5432/mydb"
Execution 
psql (14.2)
Type "help" for help.
mydb=#
  • Format is postgresql://[user[:password]@][host][:port][/dbname]
  • Secure method for credentials

psql Help and Meta-Commands

Navigate help system and use meta-commands in psql

Display All Meta-Commands

Display all available meta-commands and shortcuts

Code 
\?
Execution 
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
  • Meta-commands start with backslash
  • Command list is very long, use with pager

Get Help on SQL Commands

Show syntax and description for SQL commands

Code 
\h SELECT
Execution 
Command:     SELECT
Description: retrieve rows from a table or view
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
  • Works with all SQL keywords
  • Very useful for quick syntax lookup

List All Databases

List all databases in the PostgreSQL server

Code 
\l
Execution 
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)
  • Shows database name, owner, encoding, and privileges
  • Template databases are system templates

Describe Table Structure

Show detailed structure of a table including columns and constraints

Code 
\d users
Execution 
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)
  • Use \d+ for extended information with access privileges
  • Shows ALL constraints and indexes

Connection Environment Variables

Use environment variables to configure PostgreSQL connections

Set Connection Environment Variables

Set environment variables so psql uses them automatically

Code
Terminal window
export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=mydb
psql
Execution 
psql (14.2)
Type "help" for help.
mydb=#
  • psql reads these variables if not specified on command line
  • Command line arguments override environment variables

Using .pgpass for Password Storage

Store credentials in .pgpass file for passwordless connections

Code
Terminal window
cat ~/.pgpass
echo "localhost:5432:*:postgres:password123" >> ~/.pgpass
chmod 0600 ~/.pgpass
Execution 
localhost:5432:*:postgres:password123
  • File should contain lines in format: hostname:port:database:user:password
  • Must have 0600 permissions for security
  • Asterisk (*) matches any database

Database Management

CREATE DATABASE

Create new databases on PostgreSQL server

Create Simple Database

Create a new empty database with default settings

Code 
CREATE DATABASE my_app;
Execution 
CREATE DATABASE
  • Default encoding is UTF8 on modern PostgreSQL
  • Default owner is the current user

Create Database with Specifications

Create database with specific owner, encoding, and locale settings

Code 
CREATE DATABASE company_db
  OWNER postgres
  ENCODING 'UTF8'
  LOCALE 'en_US.UTF-8'
  TEMPLATE template0;
Execution 
CREATE DATABASE
  • 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

Create database with maximum connection limit set

Code 
CREATE DATABASE test_db
  CONNECTION LIMIT 50;
Execution 
CREATE DATABASE
  • Prevents resource exhaustion from excessive connections
  • Value of -1 allows unlimited connections

ALTER DATABASE

Modify database properties and configurations

Rename Database

Rename an existing database

Code 
ALTER DATABASE old_db RENAME TO new_db;
Execution 
ALTER DATABASE
  • Cannot rename database while connected to it
  • No connections must be active to the database

Change Database Owner

Transfer database ownership to different user

Code 
ALTER DATABASE my_db OWNER TO new_owner;
Execution 
ALTER DATABASE
  • Current owner or superuser can perform this operation

Set Connection Limit

Change the maximum number of concurrent connections

Code 
ALTER DATABASE my_db CONNECTION LIMIT 100;
Execution 
ALTER DATABASE
  • Allows adjusting limits without dropping database

DROP DATABASE

Remove databases from PostgreSQL server

Drop Database

Remove a database and all its objects permanently

Code 
DROP DATABASE my_db;
Execution 
DROP DATABASE
  • Operation is irreversible
  • No connections can be active to the database

Drop Database If Exists

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

Code 
DROP DATABASE IF EXISTS my_db;
Execution 
DROP DATABASE
  • Useful for idempotent scripts
  • Does not error if database doesn't exist

Force Drop Database with Active Connections

Forcefully terminate connections and drop database

Code 
DROP DATABASE my_db WITH (FORCE);
Execution 
DROP DATABASE
  • Available in PostgreSQL 13+
  • Forcefully disconnects all users before dropping

Table Operations

CREATE TABLE

Create tables with columns and constraints

Create Basic Table

Create users table with auto-incrementing primary key and constraints

Code 
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Execution 
CREATE TABLE
  • SERIAL automatically creates sequence for id
  • UNIQUE constraint on email prevents duplicates
  • DEFAULT CURRENT_TIMESTAMP sets creation time automatically

Create Table with CHECK Constraint

Create table with CHECK constraints to validate data

Code 
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)
);
Execution 
CREATE TABLE
  • CHECK constraint ensures price is positive
  • Multiple CHECK constraints allowed
  • Prevents invalid data at database level

Create Table with Foreign Key

Create orders table with foreign key referencing users

Code 
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
);
Execution 
CREATE TABLE
  • 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

Create custom ENUM type and use in table constraint

Code 
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)
);
Execution 
CREATE TYPE
CREATE TABLE
  • ENUM restricts column to predefined values
  • More efficient than VARCHAR with CHECK
  • Type is reusable across multiple tables

Data Types

PostgreSQL data types for different column definitions

Numeric Data Types

Create table with various numeric data types

Code 
CREATE TABLE numeric_examples (
  small_int SMALLINT,
  regular_int INTEGER,
  big_int BIGINT,
  decimal_value DECIMAL(10, 2),
  float_value FLOAT,
  serial_auto SERIAL
);
Execution 
CREATE TABLE
  • 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

Create table with various string data types

Code 
CREATE TABLE string_examples (
  char_col CHAR(10),
  varchar_col VARCHAR(255),
  text_col TEXT,
  name_col VARCHAR(100) NOT NULL
);
Execution 
CREATE TABLE
  • 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

Create table with date and time data types

Code 
CREATE TABLE datetime_examples (
  date_col DATE,
  time_col TIME,
  timestamp_col TIMESTAMP,
  timestamp_tz TIMESTAMP WITH TIME ZONE
);
Execution 
CREATE TABLE
  • 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

Create table with JSON and array data types

Code 
CREATE TABLE advanced_types (
  json_data JSON,
  jsonb_data JSONB,
  tags TEXT[],
  numbers INTEGER[]
);
Execution 
CREATE TABLE
  • 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

ALTER TABLE

Modify existing table structures and constraints

Add Column to Existing Table

Add new column to existing table

Code 
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
Execution 
ALTER TABLE
  • New column will be NULL for all existing rows
  • Can specify DEFAULT for existing rows

Add Column with Default Value

Add column with default value for existing and future rows

Code 
ALTER TABLE users
  ADD COLUMN is_active BOOLEAN DEFAULT true;
Execution 
ALTER TABLE
  • DEFAULT applies to new inserts and existing rows

Rename Table and Column

Rename both table and column names

Code 
ALTER TABLE users RENAME TO customer_users;
ALTER TABLE customer_users RENAME COLUMN phone TO phone_number;
Execution 
ALTER TABLE
ALTER TABLE
  • Rename operations are safe, referential integrity maintained
  • Consider impact on dependent views and applications

INSERT & UPDATE

INSERT Statements

Insert data into tables with various methods

Insert Single Row

Insert a single row with specified columns

Code 
INSERT INTO users (username, email)
VALUES ('john_doe', 'john@example.com');
Execution 
INSERT 0 1
  • Columns not specified will use DEFAULT or NULL
  • INSERT 0 1 means 1 row inserted with OID 0

Insert Multiple Rows

Insert multiple rows in single statement

Code 
INSERT INTO users (username, email) VALUES
  ('alice', 'alice@example.com'),
  ('bob', 'bob@example.com'),
  ('charlie', 'charlie@example.com');
Execution 
INSERT 0 3
  • Much more efficient than separate INSERT statements
  • All rows inserted atomically or none

Insert with RETURNING Clause

Insert row and return generated values immediately

Code 
INSERT INTO users (username, email)
VALUES ('diana', 'diana@example.com')
RETURNING id, username, email;
Execution 
id | username |      email
----+----------+------------------
42 | diana    | diana@example.com
(1 row)
  • RETURNING is PostgreSQL extension
  • Useful for getting auto-generated IDs
  • Can return any columns or expressions

Insert from SELECT Query

Insert rows from results of SELECT query

Code 
INSERT INTO users_backup (username, email)
SELECT username, email FROM users WHERE created_at > CURRENT_DATE - INTERVAL '30 days';
Execution 
INSERT 0 15
  • Useful for copying or archiving data
  • Can filter and transform data during copy

UPDATE Statements

Modify existing data in tables

Update Single Column

Update specific column for matching rows

Code 
UPDATE users
SET email = 'newemail@example.com'
WHERE username = 'john_doe';
Execution 
UPDATE 1
  • WHERE clause is critical to avoid updating all rows
  • UPDATE 1 means 1 row was updated

Update Multiple Columns

Update multiple columns in single statement

Code 
UPDATE users
SET
  email = 'john.doe@example.com',
  is_active = true,
  updated_at = CURRENT_TIMESTAMP
WHERE id = 1;
Execution 
UPDATE 1
  • Can update independent columns simultaneously
  • Use CURRENT_TIMESTAMP for automatic update tracking

Update with Expression

Update columns using expressions referencing current values

Code 
UPDATE products
SET price = price * 1.10, updated_at = CURRENT_TIMESTAMP
WHERE category = 'electronics';
Execution 
UPDATE 15
  • price * 1.10 increases price by 10 percent
  • Updated 15 products with category electronics

Update with RETURNING

Update and return the modified row data

Code 
UPDATE users
SET last_login = CURRENT_TIMESTAMP
WHERE username = 'alice'
RETURNING id, username, last_login;
Execution 
id | username |     last_login
----+----------+---------------------
2 | alice    | 2026-02-28 14:30:45
(1 row)
  • Confirms what was actually updated
  • Returns new values after the update

DELETE Statements

Remove data from tables

Delete Rows with Condition

Delete specific row matching condition

Code 
DELETE FROM users WHERE id = 42;
Execution 
DELETE 1
  • DELETE 1 means 1 row was deleted
  • WHERE clause is critical

Delete Multiple Rows

Delete old cancelled orders and return their details

Code 
DELETE FROM orders
WHERE status = 'cancelled'
AND created_at < CURRENT_DATE - INTERVAL '90 days'
RETURNING id, order_date;
Execution 
id |  order_date
----+---------------------
5 | 2025-11-15 10:20:30
12 | 2025-10-20 14:45:20
(2 rows)
  • Complex WHERE allows deleting old data selectively
  • RETURNING shows what was deleted before deletion

Delete All Rows

Delete all rows from table (no WHERE clause)

Code 
DELETE FROM log_entries;
Execution 
DELETE 1000
  • Very dangerous operation
  • Use TRUNCATE for faster deletion of all rows

SELECT Basics

SELECT and WHERE

Retrieve data with conditions and filtering

Select All Columns

Retrieve all columns from all rows in users table

Code 
SELECT * FROM users;
Execution 
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)
  • Using * is fine for exploration but avoid in production
  • Specify columns explicitly for performance

Select Specific Columns

Select only specific columns from table

Code 
SELECT id, username, email FROM users;
Execution 
id | username |        email
----+----------+----------------------
1 | john_doe | john@example.com
2 | alice    | alice@example.com
3 | bob      | bob@example.com
(3 rows)
  • More efficient than SELECT *
  • Only fetches needed columns

SELECT with WHERE Condition

Filter results using WHERE clause

Code 
SELECT * FROM users WHERE username = 'alice';
Execution 
id | username |       email       |     created_at
----+----------+-------------------+---------------------
2 | alice    | alice@example.com | 2026-01-20 14:25:00
(1 row)
  • String values must be in single quotes
  • Uses index on username for faster lookup

WHERE with Multiple Conditions

Filter with AND operator for multiple conditions

Code 
SELECT * FROM users
WHERE created_at > '2026-01-01'
  AND is_active = true;
Execution 
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)
  • Both conditions must be true for row to match
  • Can use OR for alternative conditions

WHERE with IN Operator

Filter for rows matching any value in list

Code 
SELECT * FROM orders
WHERE status IN ('pending', 'shipped', 'processing');
Execution 
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)
  • IN is more readable than multiple OR conditions
  • Can use subquery with IN operator

WHERE with BETWEEN

Filter for values within range (inclusive)

Code 
SELECT * FROM products
WHERE price BETWEEN 10.00 AND 100.00;
Execution 
id | name           | price
----+----------------+--------
2 | Keyboard       | 45.99
4 | Monitor        | 89.50
(2 rows)
  • BETWEEN is inclusive of both boundaries
  • Works with numbers, dates, and strings

ORDER BY and LIMIT

Sort and limit query results

Order Results Ascending

Sort results by created_at in ascending order (oldest first)

Code 
SELECT username, created_at FROM users
ORDER BY created_at ASC;
Execution 
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)
  • ASC is the default, can be omitted
  • Sorts from lowest to highest value

Order Results Descending

Sort results by created_at in descending order (newest first)

Code 
SELECT username, created_at FROM users
ORDER BY created_at DESC;
Execution 
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)
  • DESC sorts from highest to lowest value
  • Useful for getting recent records

Limit Results to First N Rows

Order by price descending and return top 5 most expensive products

Code 
SELECT * FROM products
ORDER BY price DESC
LIMIT 5;
Execution 
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)
  • LIMIT restricts number of rows returned
  • Improves performance for large result sets

Pagination with OFFSET and LIMIT

Skip first 20 rows and return next 10 (pagination)

Code 
SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 20;
Execution 
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)
  • OFFSET skips specified number of rows
  • Page 3 with page size 10 = LIMIT 10 OFFSET 20
  • Use ORDER BY for consistent pagination

JOINs & Subqueries

JOIN Types

Combine data from multiple tables using different join types

INNER JOIN Two Tables

Return only rows where both tables have matching records

Code 
SELECT users.username, orders.id, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;
Execution 
username | id | total
----------+----+--------
john_doe |  1 |100.00
alice    |  2 | 50.00
bob      |  3 | 75.00
(3 rows)
  • 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

Include all users even if they have no orders

Code 
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;
Execution 
username | order_count
----------+-------------
john_doe |           2
alice    |           1
bob      |           0
(3 rows)
  • 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

Include all orders even if user was deleted

Code 
SELECT users.username, orders.id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
Execution 
username | id
----------+----
john_doe |  1
alice    |  2
bob      |  3
         |  4
(4 rows)
  • Shows order 4 with NULL username (orphaned order)
  • Opposite of LEFT JOIN

FULL OUTER JOIN

Include rows from both tables even if no match

Code 
SELECT users.username, orders.id
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;
Execution 
username | id
----------+----
john_doe |  1
alice    |  2
bob      |  3
         |  4
(4 rows)
  • Combines LEFT and RIGHT JOIN behavior
  • Shows unmatched rows from both sides as NULL

Join with Multiple Tables

Chain multiple JOINs to combine data from 4 tables

Code 
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;
Execution 
username | id | name
----------+----+-----
john_doe |  1 | Laptop
john_doe |  1 | Mouse
alice    |  2 | Keyboard
(3 rows)
  • Each JOIN adds more conditions
  • Order of JOINs can affect performance

Subqueries

Use queries within queries for complex data retrieval

Subquery in WHERE Clause

Find users who placed orders totaling more than 100

Code 
SELECT username, email FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);
Execution 
username |        email
----------+----------------------
john_doe | john@example.com
alice    | alice@example.com
(2 rows)
  • Subquery returns list of user_ids for IN clause
  • Subquery executes first, result used by outer query

Subquery in FROM Clause

Treat subquery result as a table in FROM clause

Code 
SELECT avg_order FROM (
  SELECT AVG(total) as avg_order FROM orders
) as order_stats;
Execution 
avg_order
-----------
75.00
(1 row)
  • Subquery must have alias (order_stats)
  • Useful for complex aggregations

Scalar Subquery in SELECT

Use subquery in SELECT to get count for each user

Code 
SELECT username,
  (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) as order_count
FROM users;
Execution 
username | order_count
----------+-------------
john_doe |           2
alice    |           1
bob      |           0
(3 rows)
  • Scalar subquery must return single value per row
  • Uses correlated subquery (references outer table)
  • Can be performance intensive on large datasets

Aggregation & Grouping

Aggregate Functions

Use functions that operate on multiple rows

COUNT Aggregation

Count total number of rows in table

Code 
SELECT COUNT(*) as total_users FROM users;
Execution 
total_users
-------------
          3
(1 row)
  • COUNT(*) counts all rows including NULLs
  • COUNT(column) counts non-NULL values in column

SUM Aggregation

Sum all order totals to get total revenue

Code 
SELECT SUM(total) as total_revenue FROM orders;
Execution 
total_revenue
---------------
      225.00
(1 row)
  • SUM ignores NULL values
  • Returns NULL if no rows selected

Average and Min/Max

Calculate average, minimum, and maximum product prices

Code 
SELECT
  AVG(price) as avg_price,
  MIN(price) as min_price,
  MAX(price) as max_price
FROM products;
Execution 
avg_price | min_price | max_price
-----------+-----------+-----------
    267.37 |      9.99 |    899.99
(1 row)
  • AVG ignores NULL values
  • MIN and MAX work with any comparable data type

Multiple Aggregations

Calculate multiple aggregate metrics

Code 
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;
Execution 
order_count | unique_users | total_sales | avg_order_value
-------------+--------------+-------------+-----------------
          8 |            3 |      600.00 |          75.00
(1 row)
  • DISTINCT ensures counting unique values only
  • Multiple aggregates in single query is efficient

GROUP BY and HAVING

Group rows and filter aggregated results

GROUP BY Single Column

Group orders by user and calculate stats per user

Code 
SELECT user_id, COUNT(*) as order_count, SUM(total) as user_total
FROM orders
GROUP BY user_id;
Execution 
user_id | order_count | user_total
---------+-------------+------------
      1 |           2 |     150.00
      2 |           1 |      50.00
      3 |           3 |     200.00
(3 rows)
  • GROUP BY organizes rows into groups
  • Aggregates calculate values for each group

GROUP BY Multiple Columns

Group by date and status to see distribution

Code 
SELECT DATE(order_date) as order_day, status, COUNT(*) as count
FROM orders
GROUP BY DATE(order_date), status;
Execution 
order_day  | status | count
-----------+---------+-------
2026-02-20 | pending |     2
2026-02-20 | shipped |     1
2026-02-25 | delivered |   1
(3 rows)
  • Results grouped by both columns in GROUP BY
  • Multiple grouping columns refine the grouping

HAVING Clause Filter

Find users with more than 1 order using HAVING

Code 
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 1;
Execution 
user_id | order_count
---------+-------------
      1 |           2
      3 |           3
(2 rows)
  • 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

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

Code 
SELECT category, SUM(price) as total_price, COUNT(*) as item_count
FROM products
WHERE status = 'active'
GROUP BY category
HAVING SUM(price) > 500;
Execution 
category  | total_price | item_count
----------+-------------+------------
electronics|    1250.00 |          5
furniture |     750.00 |          3
(2 rows)
  • WHERE applies before grouping (filters rows)
  • HAVING applies after grouping (filters groups)

Advanced Queries

Window Functions

Perform calculations across rows without grouping

ROW_NUMBER for Ranking

Assign unique rank to each row based on order total

Code 
SELECT
  username,
  order_total,
  ROW_NUMBER() OVER (ORDER BY order_total DESC) as rank
FROM user_orders;
Execution 
username | order_total | rank
----------+-------------+------
alice    |      300.00 |    1
bob      |      250.00 |    2
john_doe |      200.00 |    3
(3 rows)
  • ROW_NUMBER always gives unique sequential numbers
  • Rows with same value get different row numbers

RANK with Ties

Rank with tied rows sharing same rank

Code 
SELECT
  username,
  salary,
  RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees;
Execution 
username | salary | salary_rank
----------+--------+-------------
alice    |  80000 |           1
bob      |  80000 |           1
charlie  |  75000 |           3
(3 rows)
  • RANK skips numbers after ties (1, 1, 3)
  • DENSE_RANK does not skip (1, 1, 2)

Partition Over Window

Calculate average salary per department as window function

Code 
SELECT
  department,
  username,
  salary,
  AVG(salary) OVER (PARTITION BY department) as avg_dept_salary
FROM employees;
Execution 
department | username | salary | avg_dept_salary
-----------+----------+--------+-----------------
sales      | alice    |  80000 |           75000
sales      | bob      |  70000 |           75000
it         | charlie  |  90000 |           90000
(3 rows)
  • PARTITION BY divides rows into separate windows
  • Aggregate calculated within each partition

LAG and LEAD for Sequential Access

Get previous and next order amounts for each order

Code 
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;
Execution 
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)
  • LAG accesses previous row value
  • LEAD accesses next row value
  • NULL for first/last rows without previous/next

Common Table Expressions (CTEs)

Use WITH clause to define temporary result sets

Simple CTE

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

Code 
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);
Execution 
username |        email
----------+----------------------
john_doe | john@example.com
alice    | alice@example.com
(2 rows)
  • CTE defined in WITH clause, then used in main query
  • More readable than nested subqueries
  • CTE scoped to single query only

Multiple CTEs

Define multiple CTEs and use them together

Code 
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;
Execution 
id | username | order_count
----+----------+-------------
  1 | john_doe |           2
  2 | alice    |           1
(2 rows)
  • Each CTE separated by comma
  • CTEs reference each other

Recursive CTE

Use recursive CTE to generate sequence of numbers

Code 
WITH RECURSIVE numbers AS (
  SELECT 1 as n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT * FROM numbers;
Execution 
n
---
  1
  2
  3
  4
  5
(5 rows)
  • Recursive CTE has initial query and recursive part
  • Useful for hierarchical or tree data
  • Must have termination condition to prevent infinite loop

Indexes and Views

Create indexes for performance and views for convenience

Create Index on Column

Create index on email column for faster lookups

Code 
CREATE INDEX idx_users_email ON users(email);
Execution 
CREATE INDEX
  • Greatly improves SELECT performance on indexed column
  • Increases INSERT/UPDATE time slightly
  • Should be created on frequently queried columns

Create Composite Index

Create index on multiple columns for complex queries

Code 
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date DESC);
Execution 
CREATE INDEX
  • Useful for queries filtering and sorting by these columns
  • Column order matters for query optimization

Create View

Create view for user order statistics

Code 
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;
Execution 
CREATE VIEW
  • View acts like a table but contains query logic
  • SELECT from view returns dynamically calculated results
  • Useful for common complex queries

Query a View

Query the view like a regular table

Code 
SELECT username, order_count, total_spent
FROM user_order_summary
WHERE order_count > 2;
Execution 
username | order_count | total_spent
----------+-------------+-------------
john_doe |           3 |      400.00
(1 row)
  • Views simplify complex queries
  • Can be easier to maintain than repeating complex SQL

Database Functions

String Functions

Manipulate and analyze text strings

String Concatenation

Combine multiple string columns into single value

Code 
SELECT
  CONCAT(first_name, ' ', last_name) as full_name,
  CONCAT(username, '@example.com') as email
FROM users;
Execution 
full_name  |          email
-----------+------------------------
John Doe   | john_doe@example.com
Alice Smith| alice@example.com
(2 rows)
  • CONCAT returns NULL if any argument is NULL
  • Alternative: use || operator

Substring Extraction

Extract portion of string using SUBSTRING function

Code 
SELECT
  username,
  SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) as email_prefix
FROM users;
Execution 
username | email_prefix
----------+---------------
john_doe | john
alice    | alice
(2 rows)
  • SUBSTRING(string FROM start FOR length)
  • Useful for parsing data from formatted strings

Case Conversion

Convert strings to uppercase and lowercase

Code 
SELECT
  username,
  UPPER(username) as username_upper,
  LOWER(email) as email_lower
FROM users;
Execution 
username | username_upper | email_lower
----------+----------------+--------------------
John_Doe | JOHN_DOE       | john@example.com
alice    | ALICE          | alice@example.com
(2 rows)
  • UPPER converts to uppercase
  • LOWER converts to lowercase
  • Useful for case-insensitive comparisons

String Length and Trimming

Get string length and remove leading/trailing whitespace

Code 
SELECT
  username,
  LENGTH(username) as username_length,
  TRIM(notes) as cleaned_notes
FROM users;
Execution 
username | username_length | cleaned_notes
----------+-----------------+---------------
john_doe |               8 | clean note
alice    |               5 | another note
(2 rows)
  • LENGTH returns character count
  • TRIM removes spaces, LTRIM from left, RTRIM from right

String Replacement

Replace occurrences of string within text

Code 
SELECT
  name,
  REPLACE(description, 'old_text', 'new_text') as updated_description
FROM products;
Execution 
name    | updated_description
--------+---------------------
Laptop | new_text here
(1 rows)
  • REPLACE(string, from, to) replaces all occurrences
  • Case-sensitive

Date and Time Functions

Work with dates and timestamps

Current Date and Time

Get current date and timestamp

Code 
SELECT
  CURRENT_DATE as today,
  CURRENT_TIMESTAMP as now,
  NOW() as also_now;
Execution 
today      |            now             |           also_now
-----------+----------------------------+----------------------------
2026-02-28 | 2026-02-28 14:30:45.123456 | 2026-02-28 14:30:45.123456
(1 row)
  • CURRENT_DATE returns date without time
  • CURRENT_TIMESTAMP returns timestamp with timezone
  • NOW() is alias for CURRENT_TIMESTAMP

Extract Date Components

Extract individual components from date/timestamp

Code 
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;
Execution 
created_at  | year | month | day
-----------+------+-------+-----
2026-01-15 | 2026 |     1 |  15
2026-02-28 | 2026 |     2 |  28
(2 rows)
  • EXTRACT returns numeric values for date parts
  • Can use with YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

Date Arithmetic with INTERVAL

Add and subtract intervals from dates

Code 
SELECT
  order_date,
  order_date + INTERVAL '7 days' as expected_delivery,
  CURRENT_DATE - INTERVAL '30 days' as thirty_days_ago
FROM orders;
Execution 
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)
  • INTERVAL specifies duration (days, hours, months, years)
  • Result type depends on operand types (date + interval = date)

Calculate Age Between Dates

Calculate age/duration between two timestamps

Code 
SELECT
  username,
  created_at,
  AGE(CURRENT_TIMESTAMP, created_at) as account_age
FROM users;
Execution 
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)
  • AGE returns interval showing years, months, days, etc
  • Useful for finding how old accounts or events are

Date Truncation

Truncate timestamp to specified unit

Code 
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;
Execution 
order_date  | day_start   | month_start | year_start
-----------+------------+------------+----------
2026-02-20 | 2026-02-20 | 2026-02-01 | 2026-01-01
(1 rows)
  • Useful for grouping by date parts
  • Can truncate to hour, day, month, year, etc

Math Functions

Perform mathematical calculations

Basic Math Operations

Use various math functions on price values

Code 
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;
Execution 
price | distance_from_100 | rounded_price | floor_price | ceil_price
-------+-------------------+---------------+-------------+----------
89.50 |              10.50|         89.50 |          89 |         90
(1 row)
  • ABS returns absolute value
  • ROUND(number, decimals) rounds to specified places
  • FLOOR rounds down, CEIL rounds up

Power and Square Root

Calculate power and square root of values

Code 
SELECT
  quantity,
  POWER(quantity, 2) as quantity_squared,
  SQRT(quantity) as square_root
FROM inventory;
Execution 
quantity | quantity_squared | square_root
----------+------------------+-------------
      10 |              100 |     3.16228
(1 row)
  • POWER(base, exponent) raises to power
  • SQRT returns square root

Modulo Operation

Find remainder of division and filter even IDs

Code 
SELECT
  id,
  MOD(id, 10) as remainder_of_div_10
FROM users
WHERE MOD(id, 2) = 0;
Execution 
id | remainder_of_div_10
----+-------------------
  2 |                   2
  4 |                   4
  6 |                   6
(3 rows)
  • MOD(a, b) returns remainder of a/b
  • MOD(id, 2) = 0 finds even numbers

Transactions & Performance

Transaction Control

Use transactions for data consistency

Basic Transaction

Transfer 100 units from user 1 to user 2 atomically

Code 
BEGIN;
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE users SET balance = balance + 100 WHERE id = 2;
COMMIT;
Execution 
BEGIN
UPDATE 1
UPDATE 1
COMMIT
  • Both updates succeed or both fail
  • Ensures consistency of balance transfer

Transaction Rollback

Start transaction, make changes, then rollback

Code 
BEGIN;
UPDATE products SET stock = stock - 10 WHERE id = 5;
SELECT stock FROM products WHERE id = 5;
ROLLBACK;
Execution 
BEGIN
UPDATE 1
 stock
-------
    25
(1 row)
ROLLBACK
  • ROLLBACK discards all changes in transaction
  • Stock reverts to previous value after rollback

Savepoint for Partial Rollback

Use SAVEPOINT to partially rollback failed operations

Code 
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;
Execution 
BEGIN
INSERT 0 1
SAVEPOINT
ERROR: null value in column violates not-null constraint
ROLLBACK
INSERT 0 1
COMMIT
  • Savepoint allows rolling back to previous point
  • Alice and Charlie inserted, Bob's insert rolled back

EXPLAIN and ANALYZE

Analyze query performance and execution plans

Basic EXPLAIN Plan

Show query execution plan without actually running query

Code 
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
Execution 
Seq Scan on users (cost=0.00..35.50 rows=1 width=200)
  Filter: (email = 'john@example.com'::text)
Planning Time: 0.085 ms
  • 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

Run query and show actual execution statistics

Code 
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;
Execution 
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
  • actual time shows real execution time
  • actual rows shows actual returned rows vs estimated
  • Useful for query optimization

EXPLAIN with VERBOSE and COSTS

Show detailed execution plan with output columns

Code 
EXPLAIN (ANALYZE, VERBOSE, COSTS)
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
Execution 
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
  • VERBOSE shows all output columns and filters
  • Helps understand exactly what query is doing

Query Optimization Tips

Optimize queries for better performance

Index for Common Queries

Create compound index on commonly filtered columns

Code 
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;
Execution 
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)
  • 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

Reclaim space and update query planner statistics

Code
Terminal window
VACUUM ANALYZE users;
VACUUM FULL;
Execution 
VACUUM
VACUUM
  • VACUUM removes dead rows
  • ANALYZE updates optimizer statistics
  • VACUUM FULL locks table (use during maintenance)

Batch Large Updates

Update in batches to reduce lock contention

Code 
UPDATE products
SET stock = stock - 1
WHERE id IN (SELECT product_id FROM orders WHERE status = 'shipped' LIMIT 1000);
Execution 
UPDATE 950
  • LIMIT prevents huge updates holding locks too long
  • Run multiple times to process all records

Prepared Statements

Prepare and reuse statements to prevent reparsing

Code
Terminal window
PREPARE get_user (INT) AS SELECT * FROM users WHERE id = $1;
EXECUTE get_user(1);
EXECUTE get_user(2);
DEALLOCATE get_user;
Execution 
PREPARE
 id | username |         email       | created_at
----+----------+--------------------+---------------------
  1 | john_doe | john@example.com  | 2026-01-15 10:30:00
(1 row)
PREPARE
DEALLOCATE
  • Prepared statements improve performance for repeated queries
  • Also prevent SQL injection attacks