PostgreSQL Essentials Cheatsheet

Quick Overview

PostgreSQL, often simply called Postgres, is a powerful, open-source object-relational database system known for its reliability, feature robustness, and performance. It’s a favorite among developers for its strong adherence to SQL standards, extensive feature set, and high extensibility. You’d reach for PostgreSQL when you need a stable, scalable, and feature-rich database solution for applications ranging from small web services to large data warehouses. This guide covers PostgreSQL 16, which brought significant performance improvements in query planning, bulk loading, and logical replication, alongside enhanced JSON/JSONB capabilities and monitoring.

Install PostgreSQL 16 (Debian/Ubuntu)

# Add the PostgreSQL APT repository key
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

# Add the PostgreSQL repository
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list > /dev/null

# Update package lists and install PostgreSQL 16
sudo apt update
sudo apt install postgresql-16 postgresql-contrib-16

Install PostgreSQL 16 (macOS with Homebrew)

# Update Homebrew
brew update

# Install PostgreSQL 16
brew install postgresql@16

# Start the PostgreSQL service
brew services start postgresql@16

Getting Started

Let’s get you from zero to a running PostgreSQL instance with a basic database and table.

Start/Stop PostgreSQL Service

PostgreSQL usually starts automatically after installation.

Linux (systemd-based, e.g., Ubuntu)

# Start PostgreSQL service
sudo systemctl start postgresql

# Stop PostgreSQL service
sudo systemctl stop postgresql

# Restart PostgreSQL service
sudo systemctl restart postgresql

# Check status
sudo systemctl status postgresql

macOS (Homebrew)

# Start PostgreSQL service
brew services start postgresql@16

# Stop PostgreSQL service
brew services stop postgresql@16

# Restart PostgreSQL service
brew services restart postgresql@16

# Check status
brew services info postgresql@16

Connect to PostgreSQL

By default, PostgreSQL creates a postgres user with superuser privileges and a postgres database.

# Connect to the default 'postgres' database as user 'postgres'
psql -U postgres

You’ll be dropped into the psql interactive terminal. To exit psql:

\q

Create a User and Database

It’s best practice not to use the default postgres user for applications.

-- Connect as superuser (e.g., postgres) first
psql -U postgres

-- Create a new user (role) with a password
CREATE USER myuser WITH PASSWORD 'mysecretpassword';

-- Create a new database
CREATE DATABASE myapp_db OWNER myuser;

-- Grant all privileges on the database to the new user
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myuser;

-- Connect to the new database as the new user
\c myapp_db myuser

Hello World: Create a Table, Insert Data, Query Data

-- Connect to your database as your user
psql -U myuser -d myapp_db

-- Create a simple table
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Insert some data
INSERT INTO products (name, price) VALUES
('Laptop', 1200.00),
('Mouse', 25.00),
('Keyboard', 75.50);

-- Query all data
SELECT * FROM products;

-- Query specific data
SELECT name, price FROM products WHERE price > 50.00 ORDER BY name ASC;

Core Concepts

Understanding these fundamental concepts is key to working effectively with PostgreSQL.

Concept	Description
Database	A collection of schemas, tables, views, indexes, functions, etc. The top-level organizational unit.
Schema	A namespace within a database. Organizes database objects (tables, functions) to prevent name conflicts and allow logical grouping. Default is public.
Table	A collection of related data organized into columns and rows. Stores the actual data.
Column	A vertical entity in a table, defining the type of data (e.g., INT, TEXT, DATE) and its name.
Row (Tuple)	A horizontal entity in a table, representing a single record or entry.
Index	A special lookup table that the database search engine can use to speed up data retrieval. Improves query performance on indexed columns.
Transaction	A sequence of operations performed as a single logical unit of work. Ensures Atomicity, Consistency, Isolation, Durability (ACID) properties.
Role (User)	Represents a database user or a group of users. Roles can own database objects and have specific privileges on them. Users are roles with login capabilities.
MVCC	Multi-Version Concurrency Control. Allows concurrent transactions without locking entire tables. Each transaction sees a “snapshot” of the database, minimizing conflicts and contention.

Essential Commands / API / Syntax

This section covers the 80/20 of PostgreSQL SQL and psql commands.

Database Management (psql commands)

-- List all databases
\l

-- Connect to a specific database
\c myapp_db

-- Show current database info
\conninfo

-- Create a new database
CREATE DATABASE another_db;

-- Drop a database (DANGER: irreversible)
DROP DATABASE another_db;

Schema Management

Schemas help organize objects within a database.

-- Create a new schema
CREATE SCHEMA myschema;

-- Create a table within a specific schema
CREATE TABLE myschema.mydata (id SERIAL PRIMARY KEY, value TEXT);

-- Set the search path to prioritize your schema (per session)
SET search_path TO myschema, public;

-- Show current search path
SHOW search_path;

Table Management

Create Table

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,             -- Auto-incrementing integer, primary key
    username VARCHAR(50) UNIQUE NOT NULL,   -- String up to 50 chars, must be unique and not null
    email TEXT NOT NULL,                    -- Variable-length string, non-null
    age INT,                                -- Integer
    is_active BOOLEAN DEFAULT TRUE,         -- Boolean with default value
    registration_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), -- Timestamp with timezone, default to current time
    settings JSONB                          -- JSON stored in binary format (PostgreSQL 9.4+)
);

Common Data Types

• SERIAL, BIGSERIAL: Auto-incrementing integers.
• INT, BIGINT, SMALLINT: Integer types.
• DECIMAL(p, s), NUMERIC(p, s): Exact precision numbers (e.g., for currency).
• REAL, DOUBLE PRECISION: Floating-point numbers.
• TEXT, VARCHAR(n): Variable-length strings. Use TEXT unless you have a strong reason for VARCHAR(n).
• BOOLEAN: True/False.
• DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ): Date and time types. Always prefer TIMESTAMPTZ for storing dates if timezone awareness is important.
• JSONB: Stores JSON data in a decomposed binary format, optimized for querying and indexing. New SQL/JSON syntax improvements in PostgreSQL 16.
• UUID: Universally Unique Identifier.

Alter Table

-- Add a new column
ALTER TABLE users ADD COLUMN last_login TIMESTAMP WITH TIME ZONE;

-- Drop a column
ALTER TABLE users DROP COLUMN age;

-- Alter column type (ensure data is compatible)
ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100);

-- Add a NOT NULL constraint
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Remove a NOT NULL constraint
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

-- Add a unique constraint
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

-- Rename a table
ALTER TABLE users RENAME TO app_users;

-- Rename a column
ALTER TABLE app_users RENAME COLUMN username TO user_name;

Drop Table

-- Drop a table (DANGER: irreversible)
DROP TABLE app_users;

List Tables/Describe Table (psql commands)

-- List all tables in the current database
\dt

-- Describe a table (show columns, types, indexes)
\d app_users

Data Manipulation (DML)

INSERT Data

-- Insert a single row
INSERT INTO app_users (user_name, email, is_active, settings)
VALUES ('alice', '[email protected]', TRUE, '{"theme": "dark", "notify": true}'::jsonb);

-- Insert multiple rows
INSERT INTO app_users (user_name, email, settings) VALUES
('bob', '[email protected]', '{"theme": "light"}'::jsonb),
('charlie', '[email protected]', '{"color": "blue"}'::jsonb);

SELECT Data

-- Select all columns from all rows
SELECT * FROM app_users;

-- Select specific columns
SELECT user_name, email FROM app_users;

-- Select with a WHERE clause
SELECT * FROM app_users WHERE is_active = TRUE;

-- Select with ORDER BY
SELECT user_name, registration_date FROM app_users ORDER BY registration_date DESC;

-- Select with LIMIT and OFFSET
SELECT user_name FROM app_users ORDER BY user_name ASC LIMIT 1 OFFSET 1; -- Gets the 2nd user

UPDATE Data

-- Update a single column for specific rows
UPDATE app_users SET is_active = FALSE WHERE user_name = 'charlie';

-- Update multiple columns
UPDATE app_users SET email = '[email protected]', last_login = NOW() WHERE user_name = 'bob';

DELETE Data

-- Delete specific rows
DELETE FROM app_users WHERE user_name = 'charlie';

-- Delete all rows (DANGER: irreversible, but table structure remains)
DELETE FROM app_users;

Indexing

Indexes speed up data retrieval operations. PostgreSQL 16 includes improvements to query planner optimizations, which can result in better use of indexes.

-- Create a B-tree index on a column (most common type, good for equality and range queries)
CREATE INDEX idx_user_email ON app_users (email);

-- Create a unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_unique_username ON app_users (user_name);

-- Create a GIN index on a JSONB column (for efficient querying of JSONB data)
CREATE INDEX idx_user_settings_gin ON app_users USING GIN (settings);

-- Drop an index
DROP INDEX idx_user_email;

User & Permissions

-- Create a role (can be used for users or groups)
CREATE ROLE analytics_team;

-- Grant privileges on a specific table to a role
GRANT SELECT ON app_users TO analytics_team;

-- Grant all privileges on all tables in a schema to a role
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO analytics_team;

-- Grant future privileges (for new tables)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO analytics_team;

-- Add a user to a role
GRANT analytics_team TO myuser;

-- Revoke privileges
REVOKE SELECT ON app_users FROM analytics_team;

-- List users and their roles
\du

Transactions

Transactions ensure data integrity.

-- Start a transaction
BEGIN;

-- Perform some operations
INSERT INTO products (name, price) VALUES ('Tablet', 450.00);
UPDATE products SET price = 1250.00 WHERE name = 'Laptop';

-- If all operations are successful, commit the transaction
COMMIT;

-- If something goes wrong, roll back to the BEGIN state
ROLLBACK;

Common Functions

Aggregate Functions

Used with GROUP BY to summarize data.

-- Count total products
SELECT COUNT(*) FROM products;

-- Calculate average price of products
SELECT AVG(price) FROM products;

-- Sum of all product prices
SELECT SUM(price) FROM products;

-- Find the maximum price
SELECT MAX(price) FROM products;

-- Count products by price range
SELECT
    CASE
        WHEN price < 100 THEN 'Cheap'
        WHEN price >= 100 AND price < 500 THEN 'Mid-range'
        ELSE 'Expensive'
    END AS price_category,
    COUNT(*)
FROM products
GROUP BY price_category;

String Functions

-- Convert to lowercase
SELECT LOWER('Hello World'); -- Output: 'hello world'

-- Convert to uppercase
SELECT UPPER('Hello World'); -- Output: 'HELLO WORLD'

-- Concatenate strings
SELECT CONCAT('Hello', ' ', 'Postgres'); -- Output: 'Hello Postgres'

-- Get substring
SELECT SUBSTRING('PostgreSQL', 5, 4); -- Output: 'greS' (starts at 5th char, length 4)

-- Replace parts of a string
SELECT REPLACE('Old Text', 'Old', 'New'); -- Output: 'New Text'

Date/Time Functions

-- Get current timestamp with timezone
SELECT NOW();

-- Extract parts of a date/time
SELECT EXTRACT(YEAR FROM NOW());   -- Current year
SELECT EXTRACT(MONTH FROM NOW());  -- Current month
SELECT EXTRACT(DOW FROM NOW());    -- Day of week (0=Sunday, 6=Saturday)

-- Format date/time
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');

-- Add/subtract intervals
SELECT NOW() + INTERVAL '1 day';
SELECT NOW() - INTERVAL '1 hour 30 minutes';

Common Patterns

Filtering and Sorting

-- Filter by partial string match (case-insensitive)
SELECT * FROM app_users WHERE user_name ILIKE 'a%'; -- 'alice'

-- Filter by a list of values
SELECT * FROM app_users WHERE user_id IN (1, 3);

-- Filter by range (inclusive)
SELECT * FROM products WHERE price BETWEEN 50.00 AND 500.00;

-- Ordering with NULLs
SELECT user_name, last_login FROM app_users ORDER BY last_login DESC NULLS LAST;

-- Paging results
SELECT * FROM products ORDER BY name LIMIT 10 OFFSET 20; -- Get rows 21-30

Joining Tables

Assume we have two tables: authors (author_id, author_name) and books (book_id, title, author_id).

-- Create sample tables for joins
CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name TEXT NOT NULL
);

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INT
);

INSERT INTO authors (author_name) VALUES ('Jane Doe'), ('John Smith');
INSERT INTO books (title, author_id) VALUES
('Postgres for Dummies', 1),
('SQL Masterclass', 1),
('Advanced Python', 2),
('Cooking Basics', NULL); -- Book with no author yet

-- INNER JOIN: Returns rows when there is a match in both tables
SELECT b.title, a.author_name
FROM books b
INNER JOIN authors a ON b.author_id = a.author_id;

-- LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table
SELECT b.title, a.author_name
FROM books b
LEFT JOIN authors a ON b.author_id = a.author_id;
-- Output will include 'Cooking Basics' with a NULL author_name

-- RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table
SELECT b.title, a.author_name
FROM authors a
RIGHT JOIN books b ON a.author_id = b.author_id;
-- Same output as LEFT JOIN in this case, but conceptually different depending on table order.

Window Functions

Window functions perform calculations across a set of table rows related to the current row, without grouping them into a single output row like aggregate functions. PostgreSQL 16 improves the execution efficiency of window functions.

-- Example: Calculate a running total of product prices
SELECT
    name,
    price,
    SUM(price) OVER (ORDER BY created_at) AS running_total
FROM products
ORDER BY created_at;

-- Example: Rank products by price within categories (if you had a category column)
-- For this example, let's pretend 'Laptop' and 'Keyboard' are 'Electronics' and 'Mouse' is 'Peripherals'.
-- We'll add a temporary category column.
ALTER TABLE products ADD COLUMN category TEXT;
UPDATE products SET category = 'Electronics' WHERE name IN ('Laptop', 'Keyboard');
UPDATE products SET category = 'Peripherals' WHERE name = 'Mouse';

SELECT
    name,
    category,
    price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank_in_category
FROM products;

-- Other common window functions: NTILE(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), AVG() OVER()

Working with JSONB

PostgreSQL’s JSONB type is highly optimized for querying JSON data, and PostgreSQL 16 brought expanded SQL/JSON syntax.

-- Insert data with JSONB
INSERT INTO app_users (user_name, email, settings)
VALUES ('diana', '[email protected]', '{"theme": "dark", "preferences": {"newsletter": true, "sms": false}}'::jsonb);

-- Extract a top-level field as JSONB object
SELECT settings->'theme' FROM app_users WHERE user_name = 'diana'; -- Output: "dark" (as JSONB string)

-- Extract a top-level field as TEXT
SELECT settings->>'theme' FROM app_users WHERE user_name = 'diana'; -- Output: dark (as TEXT)

-- Extract nested fields
SELECT settings->'preferences'->>'newsletter' FROM app_users WHERE user_name = 'diana'; -- Output: true

-- Check for existence of a key at the top level
SELECT * FROM app_users WHERE settings ? 'theme';

-- Check for existence of multiple keys
SELECT * FROM app_users WHERE settings ?& ARRAY['theme', 'preferences'];

-- Containment operator: Check if JSONB contains a specific structure
SELECT * FROM app_users WHERE settings @> '{"preferences": {"newsletter": true}}'::jsonb;

-- Update JSONB data (merge with existing)
UPDATE app_users
SET settings = settings || '{"last_updated": "2026-04-03"}'::jsonb
WHERE user_name = 'diana';

-- Create JSONB from columns
SELECT JSON_BUILD_OBJECT('id', user_id, 'name', user_name, 'active', is_active) FROM app_users WHERE user_name = 'alice';

-- Aggregate rows into a JSONB array
SELECT JSON_AGG(u.*) FROM app_users u WHERE u.is_active = TRUE;

Gotchas & Tips

Case Sensitivity of Identifiers

PostgreSQL folds all unquoted identifiers (table names, column names) to lowercase. If you use mixed-case or special characters, you must quote them. Always use lowercase with underscores for identifiers to avoid quoting hell.

-- Avoid this (requires quoting for every access):
CREATE TABLE "MyTable" ("MyColumn" TEXT);
SELECT "MyColumn" FROM "MyTable";

-- Prefer this:
CREATE TABLE my_table (my_column TEXT);
SELECT my_column FROM my_table;

VACUUM and ANALYZE

PostgreSQL uses Multi-Version Concurrency Control (MVCC). When a row is updated or deleted, the old version isn’t immediately removed; it becomes a “dead tuple”. VACUUM reclaims storage occupied by dead tuples, making it available for reuse. ANALYZE collects statistics about table contents for the query planner.

• autovacuum: PostgreSQL’s built-in daemon that automatically runs VACUUM and ANALYZE based on configurable thresholds. Never disable autovacuum; tune it instead.
• VACUUM FULL: Physically rewrites the table, reclaiming space to the operating system. It requires an exclusive lock on the table, blocking all reads and writes, and can be very slow. Use it sparingly during maintenance windows for significant space reclamation.
• VACUUM ANALYZE: Combines space reclamation with updating table statistics.
• PostgreSQL 16: Includes improved VACUUM processing efficiency.

-- Manually vacuum a specific table (usually autovacuum handles this)
VACUUM app_users;

-- Manually analyze a specific table (usually autovacuum handles this)
ANALYZE products;

-- Check table bloat (requires pg_stat_statements or similar extension/view for deep insight)
-- Simplified check for dead tuples
SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > (0.2 * n_live_tup); -- If dead tuples exceed 20% of live tuples, consider attention

Understanding NULL

NULL is not 0 or an empty string; it means “unknown” or “missing data”. Comparisons with NULL (e.g., column = NULL) always result in NULL, not TRUE or FALSE.

-- Correctly check for NULL values
SELECT * FROM app_users WHERE last_login IS NULL;

-- Correctly check for NOT NULL values
SELECT * FROM app_users WHERE last_login IS NOT NULL;

-- Use COALESCE to replace NULL with a default value
SELECT COALESCE(last_login, NOW()) AS effective_last_login FROM app_users;

Data Type Pitfalls

• TEXT vs VARCHAR(n): Generally, use TEXT. VARCHAR(n) enforces a maximum length, but doesn’t offer significant performance benefits and can be cumbersome if requirements change. TEXT handles variable-length strings efficiently.
• TIMESTAMP vs TIMESTAMPTZ: Always use TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) if your application deals with users in different time zones. It stores timestamps as UTC and converts to the session’s timezone upon retrieval, preventing headaches. TIMESTAMP stores local time without timezone information.
• MONEY vs NUMERIC: Do not use the MONEY type. It has currency symbols and precision issues. Always use NUMERIC(precision, scale) for monetary values.

Performance Tuning

• EXPLAIN ANALYZE: Your best friend for understanding query performance. It shows the query plan and execution statistics, helping identify bottlenecks (e.g., missing indexes, inefficient joins).

  EXPLAIN ANALYZE SELECT * FROM products WHERE price > 100 ORDER BY name;

• Indexing Strategy: Don’t over-index (writes become slower). Index columns frequently used in WHERE, JOIN, ORDER BY, or GROUP BY clauses. For JSONB, use GIN indexes.

• Connection Management: Too many database connections can consume significant resources. Use connection pooling (e.g., PgBouncer, pgPool-II) for high-traffic applications.

• Configuration (postgresql.conf, pg_hba.conf):

  • postgresql.conf: Contains server configuration parameters (e.g., shared_buffers, work_mem, wal_buffers). Tune these based on your server’s resources and workload.
  • pg_hba.conf: Controls client authentication. Ensure it’s configured securely (e.g., scram-sha-256 for password authentication) and restrict access as much as possible.

Next Steps

• Official PostgreSQL 16 Documentation: The definitive source for all features and in-depth explanations. https://www.postgresql.org/docs/16/index.html
• PostgreSQL Community Guides: Explore community-contributed guides and articles for specific use cases and advanced topics.
• Related z2h Guides:
  • SQL Essentials Cheatsheet (if it existed)
  • Docker Essentials Cheatsheet (for containerizing your Postgres app)
• Books: “PostgreSQL Up & Running” by Regina O. Obe and Leo S. Hsu is a highly recommended practical guide.

---

Source: z2h.fyi/cheatsheets/postgresql-cheatsheet — Zero to Hero cheatsheets for developers.