SQL: INSERT, UPDATE, DELETE and first steps with psql

"A lady asked for the liquid soap. The till said twelve thousand five hundred. She left. Ludovic typed one zero too many. Fix it, but don't break anything, my whole shop is in there." Until now, a wrong query returned a wrong result. From this workshop on, a wrong query destroys. It is the first workshop of the path where the interface allows writing. You will first open your first terminal, and it is not a decorative extra: what follows requires a session, that is, a continuous conversation with the database. A web interface does not show you the session; psql does, and the prompt itself changes before your eyes to tell you at every moment what you are risking. You will then trigger the disaster on purpose: an UPDATE without a WHERE clause, which sets the shop's twenty-five products to the same price in one second. Then you undo it with one word. That is the whole lesson: BEGIN before, a check in the middle, COMMIT only once you understand what you are touching. You will also discover that the row counter lies by omission. "DELETE 1" proves you deleted a single row, never that it was the right one; a false duplicate will show you. And "UPDATE 0" proves your fix never happened, which nothing else will warn you about. Finally the workshop adds writing a new row with INSERT, the primary key that physically refuses a duplicate, and the foreign key that stops you leaving a sale orphaned. A well-built database is not an obstacle to get around: it is a colleague who reviews your work and stops you when you get it wrong. It is also the bridge to the backups path, whose founding incident is exactly the UPDATE without WHERE you have just triggered safely.