INSERT

INSERT is how you add data to a table. Without it, your tables would be empty and useless. Let us look at the different ways to get data into your PostgreSQL database.

Basic Insert

The simplest way to add a row:

INSERT INTO students (first_name, last_name, email)
VALUES ('John', 'Doe', 'john@example.com');

This adds one row to the students table. We specified the column names and the corresponding values. The id column is handled automatically because we defined it as SERIAL.

Inserting Multiple Rows

You can insert several rows in a single statement:

INSERT INTO students (first_name, last_name, email, date_of_birth)
VALUES
  ('Alice', 'Smith', 'alice@example.com', '2000-05-15'),
  ('Bob', 'Johnson', 'bob@example.com', '1999-08-22'),
  ('Charlie', 'Brown', 'charlie@example.com', '2001-01-10');

This is much faster than inserting one row at a time. When you have a lot of data to add, always batch your inserts.

Inserting with Defaults

If a column has a DEFAULT value, you can skip it:

INSERT INTO students (first_name, last_name, email)
VALUES ('Diana', 'Prince', 'diana@example.com');

The enrollment_date will automatically be set to today because we defined it with DEFAULT CURRENT_DATE.

You can also explicitly use DEFAULT:

INSERT INTO students (first_name, last_name, email, enrollment_date)
VALUES ('Eve', 'Torres', 'eve@example.com', DEFAULT);

INSERT with SELECT

You can insert data from one table into another:

INSERT INTO archived_students (first_name, last_name, email)
SELECT first_name, last_name, email
FROM students
WHERE enrollment_date < '2020-01-01';

This is useful for archiving old data, creating backups, or populating summary tables.

Returning Data

PostgreSQL has a handy feature — you can get the inserted data back:

INSERT INTO students (first_name, last_name, email)
VALUES ('Frank', 'Miller', 'frank@example.com')
RETURNING id, first_name, last_name;

This returns the auto-generated ID and the values you just inserted. Very useful when you need the ID for subsequent operations.

Handling Duplicates

If you try to insert a row that violates a unique constraint, PostgreSQL will throw an error. You can handle this gracefully:

INSERT INTO students (first_name, last_name, email)
VALUES ('Grace', 'Lee', 'grace@example.com')
ON CONFLICT (email)
DO UPDATE SET first_name = EXCLUDED.first_name, last_name = EXCLUDED.last_name;

This is called an "upsert" — if the email already exists, update the name instead of failing. Very useful for syncing data.