At some point you stop thinking about SQL as "a language" and it just becomes muscle memory — you know what an UPDATE without a WHERE clause does before you even finish typing it, because you've either done it once by accident or heard someone else's horror story about it. That's basically what this article is about. Not anything exotic, just the four commands that make up most of what you actually do day to day, and the handful of ways each one bites people early on.
Why this is worth slowing down for
It's tempting to skim past CRUD because it feels like the "easy" part of SQL — everyone's written a SELECT before. But most of the actual production incidents I've read about, and a couple I've caused myself in test databases on purpose just to see what happens, come from these four commands specifically. Not from window functions or CTEs. From a missing WHERE clause, or a SELECT * that broke when someone renamed a column six months later.
The syntax is simple. The discipline around using it correctly is the part that takes longer to build.
INSERT: getting data in
INSERT INTO venues (name, latitude, longitude)
VALUES ('Van Vihar', 23.2237, 77.3910);
-- multiple rows in one call — fewer round trips, faster
INSERT INTO venues (name, latitude, longitude) VALUES
('Upper Lake', 23.2415, 77.3550),
('DB Mall', 23.2280, 77.4340);
The multi-row version is worth using whenever you're inserting more than one row at a time, even for something small. It's not just a style preference — one round trip to the database instead of several actually adds up, especially if you're populating a table with anything more than a handful of rows.
Here's a detail that connects back to how Postgres works internally: every INSERT creates a brand new row version. Postgres doesn't have a concept of "editing in place" the way you might picture it. That's tied directly to MVCC, which is a much bigger topic I'll get into properly later in this series — for now it's enough to know that even INSERT isn't as simple under the hood as "write these bytes to a table."
SELECT: the one you'll use more than everything else combined
SELECT name, latitude, longitude
FROM venues
WHERE is_closed = FALSE
ORDER BY name ASC
LIMIT 10 OFFSET 0;
WHERE filters rows before anything gets grouped or aggregated. ORDER BY sorts whatever's left. LIMIT/OFFSET handle pagination, though OFFSET gets slow once a table grows large — that's a topic for the indexing article later on, but it's worth knowing it exists as a limitation now.
The thing that trips people up early, and that I did plenty myself when I was starting out, is reaching for SELECT * out of habit. It's fine while you're exploring data in psql. It's a problem in application code, for a few reasons that aren't obvious until they bite you: it breaks silently if someone adds or renames a column later, it pulls back data you don't actually need, and it can block certain optimizations — like an index-only scan, where Postgres could answer a query using just the index without touching the table at all, but only if it knows exactly which columns you need.
UPDATE: changing what's already there
UPDATE venues
SET is_closed = TRUE
WHERE id = 5;
The one rule here that actually matters: never run an UPDATE without a WHERE clause unless you genuinely mean to update every row in the table. This is one of those things that sounds obvious written down, and then someone runs a query against production instead of the staging environment they meant to be in, and every row silently gets the same value. It happens more than you'd think, usually from copy-pasting a query and forgetting to re-add the WHERE at the end.
DELETE: same warning, different verb
DELETE FROM venues WHERE is_closed = TRUE;
Same logic as UPDATE — no WHERE means "delete everything," and Postgres will do exactly that without complaining, because as far as it's concerned that's a perfectly valid instruction. If you're ever testing a DELETE on something you're not 100% sure about, wrapping it in a transaction first (BEGIN, run the delete, check the result, then COMMIT or ROLLBACK) is a cheap habit that saves you from this exact situation.
Where it actually gets interesting: UPSERT
INSERT INTO venues (id, name, latitude, longitude)
VALUES (5, 'Van Vihar National Park', 23.2237, 77.3910)
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name;
This is Postgres's answer to "insert this row, but if it already exists, update it instead" — a pattern that comes up constantly in real projects. Syncing data from an external API is the most common case: you're pulling venue data from some third-party source on a schedule, and you don't know ahead of time whether a given venue is already in your table or not. Instead of writing a SELECT to check first and then branching into either an INSERT or an UPDATE, ON CONFLICT handles both in one statement.
EXCLUDED refers to the row that would have been inserted if there hadn't been a conflict — so EXCLUDED.name means "the name value from the row I was trying to insert." If you just want to skip duplicates entirely without updating anything, ON CONFLICT DO NOTHING does that.
If you get asked how to handle duplicate key inserts gracefully, this is the expected answer, and it's worth mentioning DO NOTHING alongside DO UPDATE since they solve slightly different problems — one is deduplication, the other is a real upsert.
Putting it together: syncing venue data from an external source
Say your dashboard pulls venue data from a city API once a day, and venues can be added, renamed, or marked closed on their end. Here's roughly how that sync would actually look, combining a few of these commands:
INSERT INTO venues (id, name, latitude, longitude, is_closed)
VALUES (12, 'Central Park Cafe', 23.2510, 77.4020, FALSE)
ON CONFLICT (id)
DO UPDATE SET
name = EXCLUDED.name,
is_closed = EXCLUDED.is_closed;
Run once per venue returned from the API, this single statement handles both new venues and updates to existing ones, without you needing to check which case you're in first. Compare that to the naive version — a SELECT to check existence, then an if/else in application code choosing between INSERT and UPDATE — and it's not just shorter, it also avoids a race condition where two sync jobs running close together could both decide the row doesn't exist yet and both try to insert it.
Where this leaves you
These four commands, plus ON CONFLICT, cover most of what you'll actually type against a database in a normal week. The syntax isn't the hard part — the discipline of always including WHERE on anything destructive, and reaching for ON CONFLICT instead of a manual check-then-branch pattern, is what actually separates code that survives contact with real data from code that doesn't.
Next up: constraints — how to make bad data structurally impossible to insert in the first place, instead of relying on catching it after the fact.
Top comments (0)