SQL / INSERTING, UPDATING, AND DELETING ROWS
Inserting a single row safely
Write a single-row INSERT that names its columns, lets defaults and identity fill the rest, and fails cleanly instead of storing a half-correct row.
What you will learn
- List the columns you supply so values bind by name instead of by physical order
- Omit a column to take its default; naming it and passing NULL overrides that default
- Read the INSERT row count as proof that exactly one row was stored
- Rely on NOT NULL, UNIQUE and CHECK: a rejected insert leaves the table untouched
Understanding Inserting a single row safely
A single-row insert has two lists that must line up: INSERT INTO member (email, city) and VALUES ('ada@example.com', 'London'). When you name the columns, the server binds each value to a column by name, so the statement keeps working after someone appends a column or restores the table with a different physical order. Omit the list and the binding is purely positional, which means the meaning of your statement depends on the table's current shape, the one thing you do not control. The tag that comes back, INSERT 0 1, is the confirmation: one row written, and the leading zero is a legacy object-id field that is always zero now.
Every column you leave out is filled by the table rather than by you: its DEFAULT expression if it has one, the next identity value if it is generated, and NULL otherwise. That makes omission a deliberate tool, because leaving out a generated key or a joined_on default is safer than inventing a value in application code, where two clients can pick the same one. It also means omitting a column and passing NULL for it are different requests: NULL is a value you chose, so a NOT NULL column rejects the row even though a perfectly good default was sitting there unused.
One INSERT is one atomic unit of work. Types, NOT NULL, UNIQUE, CHECK and foreign keys are all verified before the new row becomes visible, and if any check fails the statement raises an error and the table is left exactly as it was, with no half-written row to hunt down. That is why declared constraints are the real protection for a single insert, while checks in application code only reduce how many errors you see. What remains is the risk inside the values themselves, so build them from parameter placeholders instead of string concatenation and an apostrophe stays an apostrophe rather than becoming syntax.
CREATE TABLE member (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
city text NOT NULL DEFAULT 'unknown',
joined_on date NOT NULL DEFAULT DATE '2024-01-01'
);
-- Name the columns you supply; the table fills in the rest.
INSERT INTO member (email, city) VALUES ('ada@example.com', 'London');
-- city and joined_on are omitted, so their defaults are applied.
INSERT INTO member (email) VALUES ('grace@example.com');
SELECT id, email, city, joined_on FROM member ORDER BY id;A single-row INSERT names the columns it supplies and lets the table's defaults and constraints handle the rest, so it either lands one complete valid row or changes nothing at all.
Worked examples
A rejected insert leaves nothing behind
A duplicate key aborts the second insert entirely, so the table still holds only the first row.
CREATE TABLE seat (
row_no integer NOT NULL,
seat_no integer NOT NULL,
holder text NOT NULL,
PRIMARY KEY (row_no, seat_no)
);
INSERT INTO seat (row_no, seat_no, holder) VALUES (12, 4, 'Rivera');
INSERT INTO seat (row_no, seat_no, holder) VALUES (12, 4, 'Okafor');
SELECT count(*) AS rows_stored FROM seat;Example explained
Line 1The primary key on (row_no, seat_no) is checked while the second row is being written, before any other session could see it.
Line 2Only the first statement reports INSERT 0 1; the second reports an error instead of a command tag, which is how you tell the two apart in a script.
Line 3count(*) is 1, so the failed insert wrote no partial row and there is nothing to undo by hand.
Line 4The name seat_pkey in the message is the constraint PostgreSQL generated for the table, and application code can branch on that name to handle the collision.
Omitted, NULL, and DEFAULT are three different requests
Shows how leaving a column out, passing NULL for it, and writing DEFAULT produce different stored values.
CREATE TABLE reading (
sensor text NOT NULL,
celsius numeric NOT NULL DEFAULT 0,
note text DEFAULT 'auto'
);
INSERT INTO reading (sensor) VALUES ('s1');
INSERT INTO reading (sensor, note) VALUES ('s2', NULL);
INSERT INTO reading (sensor, celsius, note) VALUES ('s3', 21.5, DEFAULT);
SELECT sensor, note, celsius FROM reading ORDER BY sensor;Example explained
Line 1Row s1 never mentions note or celsius, so the server applies both defaults, 'auto' and 0.
Line 2Row s2 names note and hands it NULL, which wins over the default; psql prints NULL as an empty cell, not as the word NULL.
Line 3Row s3 uses the DEFAULT keyword to ask for the column default explicitly, which is what you need when the VALUES list must stay a fixed length.
Line 4celsius is numeric, so 21.5 is stored exactly as written rather than as a rounded binary float.
A value that contains an apostrophe
Doubling a single quote inside a string literal stores one apostrophe.
CREATE TABLE pub (name text NOT NULL);
INSERT INTO pub (name) VALUES ('O''Malley''s');
SELECT name, length(name) AS chars FROM pub;Example explained
Line 1Each '' inside the literal is one apostrophe in the stored value, which is why length reports 10 characters and not 12.
Line 2Writing 'O'Malley's' instead would close the string after O and leave Malley as stray tokens: ERROR: syntax error at or near "Malley".
Line 3In application code you never spell this out; you send O'Malley's as a bound parameter and the driver handles the escaping, which is the same habit that closes off SQL injection.
Important notes
Defaults are evaluated on the server at the moment the row is written, so a DEFAULT now() column records the insert time, not the time your program built the statement.
How many values you may omit without a column list is not portable: PostgreSQL fills the remaining columns with defaults, while MySQL and SQLite reject a VALUES list shorter than the table. Naming the columns makes the statement behave the same everywhere.
Common mistakes
Dropping the column list and trusting the order of VALUES: swap two text columns such as title and status and nothing errors, because both accept text, so the wrong data sits in the table until a report looks strange.
Filling in the id yourself on a serial or GENERATED BY DEFAULT AS IDENTITY key: it works at first because a manual value does not advance the sequence, then the generator reaches that number and every later insert fails with a duplicate key error.
Passing NULL to mean no value for a NOT NULL column that has a default: the default is never consulted, the row is rejected, and the fix is to drop that column from the statement entirely.
Try it yourself
Change, predict, then run
Create a table with an identity primary key, a NOT NULL text column called title, and a NOT NULL column format defaulting to 'paperback'. Insert one row supplying only title, then insert a second row that names format and passes NULL, and compare what each statement reports.
Open the SQL workspaceCheck your understanding
A table declares status text NOT NULL DEFAULT 'new'. One insert leaves status out of the column list; another names status and passes NULL. What happens?
- Both rows end up with 'new', because a default replaces any NULL that arrives
- The row that omits status gets 'new'; the row that passes NULL is rejected by the NOT NULL constraint
- Both statements fail, because a NOT NULL column must appear in every column list
- The row that omits status is rejected; the row that passes NULL gets 'new' from the default
Show answer
A default only fills in for a column the statement never mentions. Once status is named and handed NULL, that NULL is the value you asked to store, the default is skipped, and NOT NULL rejects the row. The first option is the usual misreading, treating DEFAULT as a replacement for NULL, but DEFAULT is a substitute for absence.