SQL Querying Basics

10 examples to get you started with SQL querying in PostgreSQL - 7 basic and 3 intermediate.

Prerequisites

Working app.customers table from PostgreSQL Basics or equivalent sample data.

# Local dev with Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

Basic Examples

1. Project Columns Explicitly

List the columns your API or report needs.

SELECT id, email, created_at
FROM app.customers
WHERE id = 1;

• Stable column lists survive schema additions
• ORMs can still select explicit fields
• Reduces wire size versus wide rows

2. Filter with WHERE

Predicates reduce rows before sort and join.

SELECT id, email
FROM app.customers
WHERE created_at >= now() - interval '30 days';

• Sargable predicates use indexes (no function on indexed col)
• Combine with AND/OR; watch NULL three-valued logic
• Use EXPLAIN to verify index use

3. Sort Results

ORDER BY defines deterministic ordering.

SELECT id, email, created_at
FROM app.customers
ORDER BY created_at DESC, id DESC;

• Tie-breaker columns prevent unstable pages
• DESC indexes match common feed patterns
• Sorting large sets is expensive without LIMIT

4. Limit Page Size

Cap rows returned to clients.

SELECT id, email
FROM app.customers
ORDER BY id
LIMIT 50 OFFSET 100;

• OFFSET pagination slows on deep pages
• Keyset pagination uses WHERE id > $last
• Always pair LIMIT with ORDER BY

5. Distinct Rows

Remove duplicate combinations.

SELECT DISTINCT email
FROM app.customers;

• DISTINCT is not a substitute for GROUP BY aggregates
• Consider UNIQUE constraints instead of dedup in queries
• DISTINCT ON is Postgres-specific shortcut

6. Simple Expressions

Compute derived values in SELECT.

SELECT email, lower(email) AS email_normalized,
       created_at::date AS signup_date
FROM app.customers;

• Alias columns for stable API contracts
• Casts prefer :: syntax in Postgres
• Keep expressions sargable in WHERE when indexed

7. Readable Formatting

One clause per line for reviewability.

SELECT
  c.id,
  c.email,
  c.created_at
FROM app.customers AS c
WHERE c.email LIKE '%@example.com'
ORDER BY c.created_at DESC
LIMIT 20;

• Table aliases shorten qualified columns
• Keywords uppercase is team style, not engine requirement
• Formatters (sqlfluff) help CI consistency

Intermediate Examples

8. Conditional Filters

CASE and COALESCE in projections.

SELECT id,
  CASE WHEN email LIKE '%@example.com' THEN 'corp' ELSE 'other' END AS segment
FROM app.customers;

• CASE for row-level classification
• COALESCE for NULL defaults
• Move stable CASE to generated column if hot

9. Subquery Scalar

Single-value subselect in SELECT list.

SELECT id, email,
  (SELECT count(*) FROM app.orders o WHERE o.customer_id = c.id) AS order_count
FROM app.customers c;

• Correlated subqueries can be slow at scale
• Often rewrite to JOIN or lateral
• Fine for low-volume admin screens

10. Explain a Query Plan

See planner choices before tuning.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id FROM app.customers WHERE email = 'ada@example.com';

• ANALYZE executes the query once
• BUFFERS shows cache hits
• Start here before adding indexes

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.