Belt map
Beginner belt Lesson 2 · Adding rows with INSERT
Beginner · Lesson 2 of 6

Adding rows with INSERT

Learn how to add new rows to a PostgreSQL table with INSERT.

What is INSERT?

INSERT is the SQL statement you use to add new rows to a table. While SELECT reads data, INSERT writes it — permanently and immediately.

Basic syntax

INSERT INTO table_name (column1, column2, column3)
VALUES ('value1', 'value2', 'value3');

The column list and the value list must match in order and count. If you list three columns, you must provide exactly three values.

INSERT INTO customers (first_name, last_name, email, city)
VALUES ('Ana', 'Torres', 'ana@example.com', 'Lima');

Inserting multiple rows

You can insert several rows in a single statement by separating tuples with commas:

INSERT INTO departments (name, location)
VALUES
  ('Marketing', 'Lima'),
  ('Legal', 'Santiago');

One statement, two rows — more efficient than two separate INSERT calls.

Letting the database fill columns

Not every column needs a value from you. In this dataset, id is GENERATED BY DEFAULT AS IDENTITY — the database assigns the next integer automatically. created_at on the customers table has DEFAULT now() — the database sets it to the current timestamp if you omit it.

Omit those columns entirely from your INSERT:

-- id and created_at are filled by the database
INSERT INTO customers (first_name, last_name, email, city)
VALUES ('Pedro', 'Vega', 'pedro@example.com', 'Bogota');

If you supply an explicit id, PostgreSQL does not advance the identity sequence — a later auto-generated id can collide with the one you forced. Omitting id and letting the database assign it is the normal, safe pattern.

Returning what you inserted

PostgreSQL lets you read back the newly inserted row in the same statement:

INSERT INTO customers (first_name, last_name, email, city)
VALUES ('Laura', 'Nunez', 'laura@example.com', 'Santiago')
RETURNING *;

RETURNING is useful when you need the generated id or the server-generated created_at right away — without a separate SELECT.

Try it

Run this against the classic company dataset:

INSERT INTO customers (first_name, last_name, email, city)
VALUES ('Test', 'User', 'test.user@example.com', 'Buenos Aires')
RETURNING id, first_name, email, created_at;

Notice that id and created_at appear in the result even though you did not supply them.

Real-world note

UNIQUE constraints protect data integrity. The email column on customers is declared UNIQUE, so inserting a duplicate email raises an error:

ERROR: duplicate key value violates unique constraint "customers_email_key"

Always insert only the columns you own — never forge values the database is designed to generate (identities, audit timestamps, UUIDs). If you need to handle conflicts gracefully, PostgreSQL’s INSERT ... ON CONFLICT clause is the right tool (beyond this lesson’s scope).

Summary

  • INSERT INTO table (cols) VALUES (vals) adds one row.
  • Column list and value list must match in order and count.
  • Separate tuples with commas to insert multiple rows in one statement.
  • Omit id (identity) and DEFAULT columns — the database fills them.
  • RETURNING gives you the inserted row back, including generated values.
  • UNIQUE constraints reject duplicate values — protect your data model.