Adding rows
A SELECT only reads a table; it never changes it. Three SQL commands do change it: INSERT adds new rows, UPDATE changes values in rows, and DELETE removes rows. Here they change a small library’s books table.
The library buys Piranesi. The app sends INSERT INTO books. The brackets list the columns to fill, and VALUES gives one value for each, in the same order. Text goes in single quotes; numbers do not.
The DBMS pairs each value with its column: id 6, name 'Piranesi', copies 1. It checks each value fits its column’s type, then adds the row. It replies INSERT 0 1: one row inserted.
Changing values
A reader brings back a copy of Holes. UPDATE changes rows that are already in the table. SET says which column gets which new value. WHERE says which rows to change.
The DBMS tests every row: is its id equal to 3? Only Holes has id 3, so only that row will change. The other 5 rows are left exactly as they were.
SET copies = copies + 1 reads the row’s old value, 1, adds 1 and stores 2. The id and name do not change. The DBMS replies UPDATE 1: one row changed.
Removing rows
Books with no copies left should go. DELETE FROM books removes whole rows, so it names no columns. Its WHERE says which rows: here, every row whose copies is 0.
The DBMS tests every row. Coraline and Wonder have 0 copies, so both rows are removed and the rest close up. One statement can change many rows. It replies DELETE 2.
Undo and check
Leave out WHERE and the change hits every row. UPDATE books SET copies = 0 sets all 4 rows to 0: UPDATE 4. Luckily it ran after BEGIN, which starts a transaction: nothing is final yet.
ROLLBACK cancels every change since BEGIN, so all the copies come back. COMMIT would have kept them. Without BEGIN, each statement is final at once, so check the WHERE before you run an UPDATE or DELETE.
A SELECT shows what the three changes did: Piranesi was added, Holes now has 2 copies, and Coraline and Wonder are gone. 4 rows remain.
What happens
;; step 1/11 · change SELECT reads rows (changes nothing) INSERT adds new rows UPDATE changes values in rows already there DELETE removes whole rows table books, before any change: id name copies 1 Dune 2 2 Coraline 0 3 Holes 1 4 The Road 3 5 Wonder 0
In INSERT 0 1 the 0 is a leftover: it used to be an internal object id and is now always 0. The 1 is the number of rows inserted. psql shows these replies; drivers give you the row count. Without ORDER BY, SQL does not promise any order for the rows of a result. The figure keeps the rows in id order; the last query asks for that with ORDER BY id.