Inserting data: INSERT

So far you've been reading data. It's time to write some. INSERT is the command that adds new rows to a table.

The basic syntax

INSERT INTO tabella (col1, col2, …)
VALUES (val1, val2, …);

Example on our dataset:

INSERT INTO customers (first_name, last_name, email, city, country, signed_up_on)
VALUES ('Marco', 'Bianchi', 'marco.bianchi@example.com', 'Verona', 'Italia', '2025-07-15');

Naming the columns is the recommended practice: it makes the query robust against future schema changes (e.g. a new optional phone column doesn't break anything) and self-documenting.

Multi-row INSERT

A single statement, multiple rows — much faster than N separate INSERTs:

INSERT INTO categories (name, parent_id) VALUES
  ('Gaming',         1),
  ('Smart Home',     1),
  ('Cucina vegana',  8);

RETURNING: reading the values you just inserted

A clause characteristic of PostgreSQL: RETURNING returns the rows that were just created. Extremely handy for retrieving the auto-generated id:

INSERT INTO categories (name, parent_id)
VALUES ('Gaming', 1)
RETURNING id, name;

The output is a one-row table with id (assigned by SERIAL) and name.

What happens if a column is missing

If you omit a column in the INSERT:

• if it is NOT NULL without a DEFAULT → error;
• if it has a DEFAULT (including SERIAL for id) → that is used;
• if it accepts NULL → it becomes NULL.

-- Va bene: id è SERIAL (auto), country ha NOT NULL ma forniamo tutti i campi
INSERT INTO customers (first_name, last_name, email, city, country, signed_up_on)
VALUES ('Olivia', 'Marini', 'olivia.marini@example.com', 'Bari', 'Italia', '2025-07-20');

-- Errore: first_name è NOT NULL e non è fornito
INSERT INTO customers (last_name, email, city, country, signed_up_on)
VALUES ('Marini', 'olivia.marini@example.com', 'Bari', 'Italia', '2025-07-20');

Your turn

Review exercise