Core SQL: Querying and Manipulating DataLesson 2.2

INSERT UPDATE DELETE in PostgreSQL with examples

INSERT INTO VALUES, INSERT with SELECT, UPDATE SET WHERE, DELETE WHERE, RETURNING clause, upsert with ON CONFLICT

Modifying Data

INSERT, UPDATE, and DELETE are the write operations in SQL. The RETURNING clause makes them significantly more useful in application code.

INSERT

INSERT INTO users (email, username)
VALUES ('alice@example.com', 'alice'),
       ('bob@example.com',   'bob')
RETURNING id, created_at;

UPDATE

UPDATE employees
SET    salary = salary * 1.10,
       updated_at = now()
WHERE  department_id = 3
RETURNING id, salary;

Always include a WHERE clause in UPDATE. Without it you update every row in the table.

DELETE

DELETE FROM sessions
WHERE  expires_at < now()
RETURNING id;

Upsert with ON CONFLICT

INSERT INTO page_views (page_id, view_count)
VALUES (42, 1)
ON CONFLICT (page_id)
DO UPDATE SET view_count = page_views.view_count + 1;

ON CONFLICT DO NOTHING silently skips duplicates. ON CONFLICT DO UPDATE is a true upsert — insert if not exists, update if exists. The conflict target must match a unique constraint or primary key column.