Changing what is there

Reading is the safe half. These three statements change data, and two of them are one missing clause away from an incident.

Insert

INSERT INTO customers (name, city) VALUES ('Priya', 'Chennai');

INSERT INTO customers (name, city) VALUES
    ('Arun', 'Madurai'),
    ('Meera', 'Kochi');

Always name the columns. INSERT INTO customers VALUES (...) depends on column order, so adding a column to the table silently breaks every such statement.

Inserting many rows in one statement is dramatically faster than many statements, because each statement is a round trip and often its own transaction.

Update

UPDATE orders SET status = 'shipped' WHERE id = 42;

Without the WHERE, every row is updated. There is no confirmation and no undo outside a transaction.

The habit that prevents it costs five seconds:

BEGIN;
SELECT count(*) FROM orders WHERE id = 42;   -- expect 1, see 1
UPDATE orders SET status = 'shipped' WHERE id = 42;
-- check the reported row count, then
COMMIT;

Run the SELECT with the same WHERE first. If it returns 400,000 rows, you have learned that before rather than after.

Delete

DELETE FROM orders WHERE created_at < '2020-01-01';

Same danger, and worse, because an update can sometimes be reasoned back and a delete cannot.

TRUNCATE is much faster than DELETE without a WHERE because it does not process rows individually. It also fires no row triggers, which is occasionally the difference between a correct system and a broken one.

Upsert

Inserting if absent and updating if present, in one statement without a race:

INSERT INTO settings (user_id, theme) VALUES (7, 'dark')
ON CONFLICT (user_id) DO UPDATE SET theme = EXCLUDED.theme;

Doing it as a SELECT then an INSERT leaves a window where another connection inserts between the two, which is a duplicate key error in production and never in testing.