Introduction

A compact reference for writing PostgreSQL queries, perfect for quick recall during projects, interviews, and practice.

PostgreSQL
The world's most advanced open source relational database

Why PostgreSQL?

  • Open-source relational database management system
  • Strong reputation for reliability and data integrity
  • Robust feature set including JSON support
  • Excellent performance for complex queries
  • Active community and extensive documentation

How to Use This Guide

This cheat sheet is organized by topic, with each section containing common commands and examples.

Use the navigation menu to jump between sections, or try out commands in the SQL Playground.

Tip: Click any code snippet to copy it to your clipboard!

Getting Started

psql -U username -d database_name

Connect to PostgreSQL using the command line client. Replace username and database_name with your credentials.

Ready to dive in? Navigate through the sections using the menu to explore PostgreSQL commands and concepts.

DB Database Operations

-- Create a new database
CREATE DATABASE dbname;

-- Drop a database
DROP DATABASE dbname;

Table Table Operations

-- Create a new table
CREATE TABLE table_name (
    column1 datatype CONSTRAINT,
    column2 datatype CONSTRAINT,
    ...
);

-- Drop a table
DROP TABLE table_name;

-- Alter a table (add, drop, rename columns)
ALTER TABLE table_name ADD COLUMN new_column datatype;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Types Data Types (Common)

PostgreSQL supports a wide range of data types for storing different kinds of information. Choosing the right type ensures data integrity and efficient storage.

  • INTEGER – Whole numbers, e.g., age INTEGER
  • SERIAL – Auto-incrementing integer, often used for primary keys
  • VARCHAR(n) – Variable-length string with a limit, e.g., name VARCHAR(50)
  • TEXT – Unlimited-length string
  • BOOLEAN – TRUE or FALSE
  • DATE, TIME, TIMESTAMP – Date and time values
  • NUMERIC(precision, scale) – Exact numbers, e.g., price NUMERIC(10,2)
Example: 
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email TEXT UNIQUE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW()
);

This creates a users table with various data types and constraints.

Constraints

-- NOT NULL
column_name datatype NOT NULL

-- UNIQUE
column_name datatype UNIQUE

-- PRIMARY KEY
column_name datatype PRIMARY KEY
-- or as table-level
PRIMARY KEY (column1, column2)

-- FOREIGN KEY
FOREIGN KEY (col_name) REFERENCES other_table(col)

-- CHECK
CHECK (condition)

-- DEFAULT
column_name datatype DEFAULT value

Inserting Data

-- Insert single row
INSERT INTO table_name (col1, col2) VALUES (val1, val2);

-- Insert multiple rows
INSERT INTO table_name (col1, col2) VALUES (val1, val2), (val3, val4);

Basic Queries

-- Select all
SELECT * FROM table_name;

-- Select specific columns
SELECT col1, col2 FROM table_name;

-- Filtering rows
SELECT * FROM table_name WHERE condition;

-- Aliases
SELECT col1 AS alias_name FROM table_name;

WHERE Clause & Operators

The WHERE clause is used to filter records that meet certain conditions. Combine multiple conditions with logical operators for more complex queries.

Basic Usage: 
SELECT * FROM employees WHERE department = 'IT';

Returns all employees in the IT department.

Logical Operators: 
SELECT * FROM employees WHERE salary > 50000 AND is_active = TRUE;

Returns active employees with a salary above 50,000.

SELECT * FROM employees WHERE department = 'HR' OR department = 'Finance';

Returns employees in either HR or Finance.

SELECT * FROM employees WHERE NOT (is_active);

Returns employees who are not active.

Comparison Operators: 
-- SQL code here

Sort Sorting, Limiting, Offsetting ❓

Use ORDER BY to sort results, LIMIT to restrict the number of rows, and OFFSET to skip rows.

Sort Results: 
SELECT * FROM products ORDER BY price ASC;

Lists products sorted by price from lowest to highest.

Limit and Offset: 
SELECT * FROM products ORDER BY price DESC LIMIT 5 OFFSET 10;

Returns 5 products, skipping the first 10, sorted by price from highest to lowest.

UPDATE & DELETE

Use UPDATE to modify existing records and DELETE to remove records. Always use a WHERE clause to avoid affecting all rows.

Update Example: 
UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT';

Increases salary by 10% for all IT employees.

Delete Example: 
DELETE FROM employees WHERE is_active = FALSE;

Removes all employees who are not active.

Pattern Matching & Ranges

-- LIKE & ILIKE (case-insensitive)
WHERE col LIKE 'A%';
WHERE col ILIKE '_ello';

-- BETWEEN
WHERE col BETWEEN 10 AND 20;

-- IN / NOT IN
WHERE col IN (val1, val2);

NULL Checks

WHERE col IS NULL;
WHERE col IS NOT NULL;

Aggregate Functions

Aggregate functions perform calculations on multiple rows and return a single value. Commonly used with GROUP BY.

  • COUNT(*) – Number of rows
  • SUM(col) – Total sum of a column
  • AVG(col) – Average value
  • MIN(col), MAX(col) – Minimum/maximum value
Example: 
SELECT department, COUNT(*) FROM employees GROUP BY department;

Counts employees in each department.

Group By GROUP BY and HAVING ❓

GROUP BY is used to group rows that have the same values in specified columns. HAVING is used to filter groups based on a condition.

Group and Count: 
SELECT department, COUNT(*) FROM employees GROUP BY department;

Counts the number of employees in each department.

Group with Condition: 
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 60000;

Shows departments with an average salary above 60,000.

Join Joins ❓

-- INNER JOIN
SELECT * FROM a INNER JOIN b ON a.id = b.a_id;

-- LEFT JOIN
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;

-- RIGHT JOIN
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;

-- FULL OUTER JOIN
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id;

-- CROSS JOIN
SELECT * FROM a CROSS JOIN b;

Set Operations

-- UNION (removes duplicates)
SELECT col FROM a UNION SELECT col FROM b;

-- UNION ALL (keeps duplicates)
SELECT col FROM a UNION ALL SELECT col FROM b;

-- INTERSECT
SELECT col FROM a INTERSECT SELECT col FROM b;

-- EXCEPT
SELECT col FROM a EXCEPT SELECT col FROM b;

Subqueries

-- Scalar Subquery
SELECT * FROM table WHERE col = (SELECT MAX(col) FROM table);

-- Correlated Subquery
SELECT * FROM a WHERE EXISTS (SELECT 1 FROM b WHERE a.id = b.a_id);

Views

-- Create view
CREATE VIEW view_name AS SELECT col FROM table;

-- Drop view
DROP VIEW view_name;

Transactions

BEGIN;
UPDATE ...;
DELETE ...;
COMMIT;
-- or
ROLLBACK;

Indexes

CREATE INDEX idx_name ON table_name (col);
DROP INDEX idx_name;

Export / Import

Use pg_dump to export (backup) a database and psql to import (restore) it.

Export Example: 
pg_dump mydb > backup.sql

Exports the mydb database to a file called backup.sql.

Import Example: 
psql mydb < backup.sql

Restores the mydb database from the backup.sql file.

psql Commands

\l        -- list databases
\c dbname -- connect to database
\dt       -- list tables
\d table  -- describe table
\q        -- quit

Functions & Expressions (Basic)

PostgreSQL provides many built-in functions for math, strings, and dates. Use them in SELECT statements to transform or analyze data.

Math Functions: 
SELECT ROUND(1.5), FLOOR(1.9), CEIL(1.2);

Rounds, floors, or ceils the given numbers.

String Functions: 
SELECT LENGTH('abc'), UPPER('abc'), LOWER('ABC'), CONCAT('a','b');

Gets string length, converts to upper/lower case, or concatenates strings.

Date/Time Functions: 
SELECT NOW(), CURRENT_DATE, CURRENT_TIME;

Returns the current date and time.

Window Functions (Intro)

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC)
FROM employees;

Cheat Sheet Summary

Database

CREATE DATABASE dbname;
DROP DATABASE dbname;

Table

CREATE TABLE ...
DROP TABLE ...

Insert

INSERT INTO ... VALUES ...

Select

SELECT * FROM ...

Update

UPDATE ... SET ... WHERE ...

Delete

DELETE FROM ... WHERE ...

Playground SQL Playground

Sample Database: employees

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    department_id INTEGER,
    salary INTEGER,
    is_active BOOLEAN
);

-- Sample Data
INSERT INTO employees (name, department_id, salary, is_active) VALUES
('Alice', 1, 70000, TRUE),
('Bob', 2, 50000, TRUE),
('Carol', 3, 60000, FALSE),
('David', 1, 80000, TRUE),
('Eve', 3, 65000, TRUE);

Sample Database: departments

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(30)
);

-- Sample Data
INSERT INTO departments (name) VALUES
('IT'),
('HR'),
('Finance');

Developer Developer Info

Eyob Weldetensay

Eyob Weldetensay

Software Engineering Student
Arba Minch University

Passionate about databases, backend, and building useful tools for developers.

💡 Tips

  • Always backup before DROP or DELETE.
  • Use EXPLAIN to analyze query performance.
  • Use transactions when modifying multiple rows.
  • Write readable SQL with indentation.
🔥 End of Cheat Sheet