PostgreSQL and SQL 101: the commands and habits that matter

SQL expresses the data result you need

SQL is a declarative language: describe the rows and columns you need, and the database chooses a plan for finding them. This is different from navigating records one at a time in application code.

PostgreSQL stores data in databases, schemas, tables, rows and columns. A schema is a namespace; applications commonly use the default public schema, though a named schema can clarify ownership in a larger database.

A table should represent one kind of fact. The columns describe that fact, while constraints state what must always be true. Good constraints prevent invalid data from entering through a script, admin tool or future application path.

Create tables with keys and constraints

A primary key identifies one row. A foreign key records that one row refers to a valid row in another table. NOT NULL, CHECK and UNIQUE constraints capture rules that should not depend only on an application form.

Use a type that matches the meaning of the value. PostgreSQL has integers, numeric values, text, boolean, date and timestamp types, UUID, JSONB and many others. Do not reach for text merely because it accepts everything.

Migrations should be versioned alongside the application. A database structure that exists only because someone ran commands manually cannot be reliably recreated in another environment.

create table customer (
  id uuid primary key,
  email text not null unique,
  created_at timestamptz not null default now()
);

create table purchase (
  id uuid primary key,
  customer_id uuid not null references customer(id),
  amount numeric(12, 2) not null check (amount > 0),
  status text not null check (status in ('pending', 'paid', 'cancelled')),
  created_at timestamptz not null default now()
);

Select only the data you need

SELECT reads rows. Name the columns you need rather than using SELECT *, especially in application queries. Explicit columns make a query stable when a table gains a new large or sensitive field.

WHERE filters rows, ORDER BY makes order deliberate and LIMIT bounds the result. Without ORDER BY, a database is free to return matching rows in any order, even when it happened to look stable during development.

Use parameters from your database library rather than building SQL strings with user input. Parameters protect against injection and let PostgreSQL distinguish the query structure from its values.

select id, amount, status, created_at
from purchase
where customer_id = $1
  and status = 'paid'
order by created_at desc
limit 20;

Join related facts deliberately

A JOIN combines rows using a relationship. An INNER JOIN returns only rows with a match on both sides. A LEFT JOIN keeps every row from the left side and fills unmatched right-side columns with NULL.

Joins are powerful because relational data is normalised: a customer fact is stored once, while purchases refer to it. They can also multiply rows when joining a one-to-many relationship, so check whether the result is at the row level you expect.

Aggregate after deciding the correct join and filter. GROUP BY creates one result row per group, while aggregate functions such as count, sum, min and max summarise values in that group.

select c.email, count(p.id) as purchase_count, sum(p.amount) as total_paid
from customer c
join purchase p on p.customer_id = c.id
where p.status = 'paid'
group by c.id, c.email
order by total_paid desc;

Change rows with a narrow condition

INSERT creates rows, UPDATE changes matching rows and DELETE removes matching rows. The dangerous part of UPDATE and DELETE is not their syntax; it is forgetting or weakening the WHERE clause.

Before a material change, run the same condition as a SELECT and inspect the row count. Use RETURNING to see precisely what changed. In a production environment, execute work through reviewed migrations or operational procedures rather than an unrecorded console session.

Prefer a status transition or archival column when the business needs a history of what happened. Deleting an important record can make support, audit and recovery much harder.

update purchase
set status = 'paid'
where id = $1
  and status = 'pending'
returning id, status, amount;

Transactions make related changes atomic

A transaction groups statements so they either all succeed or none do. It protects a unit of work from being left halfway through when a later statement fails.

Transactions also define how concurrent work sees data. Two requests can both read an available balance and attempt an update, so important invariants need an appropriate database constraint, lock or conditional update—not merely a transaction wrapper.

Keep transactions short. Do not hold one open while waiting for an HTTP request, a user action or a slow external service. Those waits retain database connections and locks while doing no database work.

begin;

update account
set balance = balance - $2
where id = $1
  and balance >= $2;

-- Check that exactly one row changed before continuing.

update account
set balance = balance + $2
where id = $3;

commit;

Indexes support access patterns, not every column

An index is an additional data structure that can help PostgreSQL find rows without scanning a whole table. It speeds some reads but adds storage and write cost, because inserts, updates and deletes must maintain it.

Index columns used often in selective WHERE clauses, joins and useful ordering. A composite index is ordered by its leading columns, so an index on (customer_id, created_at) supports lookups by customer and ordered customer history more directly than one on created_at alone.

Use EXPLAIN ANALYZE with representative data to understand a slow query. Do not add indexes solely because a column sounds important; a small table or low-selectivity condition may be cheaper to scan.

create index purchase_customer_created_at_idx
on purchase (customer_id, created_at desc);

explain analyze
select id, amount, status, created_at
from purchase
where customer_id = $1
order by created_at desc
limit 20;

Use psql for quick, explicit inspection

psql is PostgreSQL’s command-line client. It can connect with a connection string or named options, execute a file and provide useful meta-commands for inspecting a database.

Meta-commands begin with a backslash and are interpreted by psql rather than SQL. Use them to list databases, relations and table definitions before writing a query against an unfamiliar environment.

Treat production access as an operational capability: use the least privilege needed, avoid copying sensitive data into local files and record important changes through the normal engineering process.

psql "$DATABASE_URL"
\l                 -- list databases
\c app_db          -- connect to a database
\dn                -- list schemas
\dt                -- list tables
\d+ purchase       -- describe a table
\x on              -- expanded output
\timing on         -- show query duration
\q                 -- quit

Make the database part of the application design

PostgreSQL is not merely a place where objects are stored. Its constraints, transaction semantics, query plans and backup strategy affect what the application can safely promise.

Keep SQL close enough to the code that owns the behaviour for engineers to review it. Test important queries against a real PostgreSQL instance, especially queries involving JSON, time zones, locking and migrations; an in-memory substitute will not always behave the same way.

The basic discipline is durable: model facts clearly, constrain invalid states, parameterise values, inspect query plans when evidence calls for it and make every production change repeatable.

• Use explicit columns instead of SELECT * in application queries
• Run a SELECT before an UPDATE or DELETE with a new condition
• Use RETURNING to inspect a material data change
• Store schema changes as versioned migrations
• Use database constraints for rules that must always hold
• Measure a slow query with EXPLAIN ANALYZE before changing indexes