Everything so far only read data. Now we change it. The three statements are simple, and the playground below resets cleanly (hit Reset any time to restore the original data), so this is the place to be fearless.
INSERT: add new rows
INSERT INTO table (columns...) VALUES (...) adds a row. You list the columns, then the matching values. Here we add a product and immediately read the category back to see it landed.
Press Run to execute your query.
Notice the playground ran two statements: an INSERT and then a SELECT. The output shows the result of the last query that returns rows. Try inserting another product (give it a new id, since id is the primary key and must be unique) and run again. Insert one with an id that already exists and you'll get an error, which is the database protecting you from duplicates.
UPDATE: change existing rows
UPDATE table SET column = value WHERE condition changes the rows that match the condition. Here we trim the price of every Stationery item.
Press Run to execute your query.
The most dangerous missing word in SQL
An UPDATE or DELETE without a WHERE clause affects every row in the table. UPDATE products SET price = 0; zeroes out all prices, instantly, no undo. This is one of the most common ways people wreck production data. Before you run a write, look for the WHERE and ask "does this match exactly the rows I mean?" A good habit: write the matching SELECT first, confirm it returns the right rows, then turn it into the UPDATE or DELETE.
Edit the query above to remove the WHERE line, run it, and watch every product's price drop. Then hit Reset to undo it. That's the whole lesson about WHERE in one experiment.
DELETE: remove rows
DELETE FROM table WHERE condition removes matching rows. The same WHERE warning applies, doubly. But there's a second guard you'll meet here: foreign keys.
Press Run to execute your query.
This one deletes from order_items before deleting the order. Try deleting just the order first (remove the first line) and you'll get a foreign-key error. That's the database refusing to leave order_items pointing at an order that no longer exists.
Foreign keys: the database protects relationships
A foreign key is a rule that says "this column must point at a real row in another table." order_items.order_id must match an existing orders.id. Because of that rule, the database won't let you delete an order while items still reference it, since that would orphan those items. You either delete the dependents first (as above), or set up the foreign key to cascade (automatically delete dependents), a choice you make when designing the table.
Writes, summarised
INSERT adds rows (respecting unique keys), UPDATE changes the rows a WHERE selects, DELETE removes them. The WHERE clause is everything: omit it and you hit the whole table. Foreign keys stop you from leaving dangling references. When in doubt, SELECT the rows first to see exactly what you're about to change, and remember Reset is there because experimenting is how this sticks.
Next: your queries work, but are they fast? The final hands-on lesson is about indexes, and you'll ask Postgres itself how it runs your query.
Test yourself
Questions· say the answer out loud before you open it. If you can't, the chapter isn't done.
QWhat does an INSERT with a duplicate primary key do?+
It errors. The primary key must be unique, so inserting a row whose id already exists is rejected. That's the database protecting you from duplicate records; you give the new row a fresh, unused id.
QWhy is a missing WHERE on UPDATE or DELETE so dangerous?+
Because without WHERE, the statement applies to every row in the table. UPDATE products SET price = 0; zeroes all prices and DELETE FROM orders; removes all orders, instantly and with no undo. Always check the WHERE matches exactly the rows you intend, ideally by running the equivalent SELECT first.
QWhat is a foreign key and how does it protect you?+
A rule that a column must point at a real row in another table (order_items.order_id must match an existing orders.id). It stops you from deleting or changing a row in a way that would leave orphaned references, like deleting an order whose items still point at it.
QYou need to delete an order that has items. What happens and what do you do?+
Deleting the order directly fails with a foreign-key error, because order_items still reference it. You delete the dependent order_items first, then the order, or you design the foreign key to cascade so dependents are removed automatically.
QWhat's a safe habit before running an UPDATE or DELETE?+
Write the matching SELECT first with the same WHERE, confirm it returns exactly the rows you mean, then convert it to the UPDATE or DELETE. This catches a wrong or missing WHERE before it touches data, and in a real database you'd also run inside a transaction you can roll back.
Comments
Loading comments…