INSERT, UPDATE, and DELETE
INSERT adds rows, UPDATE changes matching rows, and DELETE removes matching rows. A missing or incorrect WHERE clause on an update or delete can affect every row. PostgreSQL's RETURNING clause can return changed rows without requiring a separate query, which is useful for generated IDs and API responses.
For multi-step operations, use transactions when the steps must succeed or fail together. In application code, use parameterized queries rather than concatenating user input into SQL strings.
Modify rows safely
INSERT INTO products (name, price)
VALUES ('Keyboard', 2499.00)
RETURNING id, name, price;
UPDATE products
SET price = 2299.00
WHERE name = 'Keyboard'
RETURNING id, price;💡 RETURNING provides the inserted or updated values. In real application code, pass the product name and price as parameters rather than embedding dynamic input in the SQL text.
Key Takeaways
- Review the WHERE clause before UPDATE or DELETE.
- RETURNING can return affected rows and generated values.
- Use parameterized queries for dynamic values.
Knowledge Check
Which PostgreSQL clause can return the rows affected by an INSERT or UPDATE?