Modifying Data: INSERT, UPDATE, DELETE

Data Manipulation Language (DML) is how you interact with the rows inside your tables.

INSERT

The INSERT INTO statement is used to add new rows to a table.

Basic Insert

You must specify the table name, the columns you are providing values for, and the values themselves.

INSERT INTO users (first_name, last_name, email)
VALUES ('John', 'Doe', '[email protected]');

Multi-Row Insert

You can insert multiple rows in a single statement, which is highly efficient.

INSERT INTO users (first_name, last_name, email)
VALUES 
    ('Jane', 'Smith', '[email protected]'),
    ('Alice', 'Wonderland', '[email protected]');

RETURNING Clause (PostgreSQL Specific)

PostgreSQL allows you to return data from the rows you just modified using the RETURNING clause. This is incredibly useful for getting the autogenerated SERIAL Primary Key back to your application immediately.

INSERT INTO users (first_name, last_name)
VALUES ('Bob', 'Builder')
RETURNING id, created_at;

UPDATE

The UPDATE statement is used to modify existing records.

UPDATE users
SET 
    first_name = 'Jonathan',
    email = '[email protected]'
WHERE id = 1;

CAUTION

If you omit the WHERE clause in an UPDATE statement, ALL ROWS in the table will be updated. Always double-check your WHERE condition.

Like INSERT, you can use RETURNING with UPDATE.

DELETE

The DELETE statement is used to remove existing records.

DELETE FROM users
WHERE id = 5;

CAUTION

If you omit the WHERE clause, ALL ROWS will be deleted.

TRUNCATE vs DELETE

If you genuinely want to delete all rows in a table, do not use DELETE FROM table_name;. Use TRUNCATE.

TRUNCATE TABLE users;

Why? DELETE scans the table and logs the deletion of each individual row. TRUNCATE simply drops the data files on disk holding the table data and recreates empty ones. TRUNCATE is virtually instantaneous, regardless of whether the table has a hundred rows or a billion rows.

Upsert (ON CONFLICT)

Often, you want to insert a row, but if it already exists (e.g., violating a UNIQUE constraint), you want to update it instead. This is called an "Upsert". In PostgreSQL, this is achieved using ON CONFLICT.

INSERT INTO users (id, login_count)
VALUES (1, 1)
ON CONFLICT (id) 
DO UPDATE SET login_count = users.login_count + 1;