PostgreSQL INSERT Statement

Once a table exists in PostgreSQL, the next step is to add data into it. The INSERT statement is used to add one or more rows of data into a table. Every piece of data stored in a database begins with an INSERT operation.

Basic Syntax of INSERT

The standard structure of an INSERT statement looks like this:

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

The column names listed in parentheses after the table name must match the values listed in the VALUES clause — both in order and in count. Each value corresponds to the column in the same position.

Inserting a Single Row

Consider a table called students with three columns: id, name, and age.

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

To add one student to this table:

INSERT INTO students (name, age)
VALUES ('Alice', 22);

Notice that the id column is not included. Because it is defined as SERIAL, PostgreSQL automatically generates a unique number for it. Only the columns that require manual input need to be listed.

Inserting Multiple Rows at Once

Multiple rows can be inserted in a single INSERT statement by separating each set of values with a comma:

INSERT INTO students (name, age)
VALUES
    ('Bob', 20),
    ('Carol', 23),
    ('David', 21);

This is more efficient than running three separate INSERT statements. All three rows are added in one operation.

Inserting Data into All Columns

If values are being provided for every column in the table, the column names can be omitted. However, the values must be listed in the exact order the columns were defined in the table:

INSERT INTO students
VALUES (5, 'Eva', 24);

This approach is error-prone if the table structure changes later, so explicitly listing column names is a better habit.

Using DEFAULT Values

When a column has a default value defined, the keyword DEFAULT can be used as a placeholder:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    in_stock BOOLEAN DEFAULT TRUE
);

INSERT INTO products (name, in_stock)
VALUES ('Notebook', DEFAULT);

PostgreSQL will substitute TRUE for the in_stock column automatically.

INSERT with RETURNING Clause

PostgreSQL has a useful extension to the standard SQL INSERT: the RETURNING clause. It returns data from the row that was just inserted, which is especially helpful for retrieving auto-generated values like IDs.

INSERT INTO students (name, age)
VALUES ('Frank', 25)
RETURNING id, name;

The result will display the newly assigned id and the name of the inserted row without running a separate SELECT query.

Inserting Data from Another Table

Data from one table can be copied into another using INSERT with a SELECT statement:

INSERT INTO archived_students (name, age)
SELECT name, age FROM students
WHERE age > 23;

This inserts all students older than 23 from the students table into the archived_students table. No VALUES clause is needed when using SELECT as the data source.

Handling Conflicts with ON CONFLICT

Sometimes an insert operation may conflict with an existing row — for example, trying to insert a duplicate primary key or a value that violates a unique constraint. PostgreSQL provides the ON CONFLICT clause to handle these situations gracefully.

Do Nothing on Conflict

INSERT INTO students (id, name, age)
VALUES (1, 'Alice', 22)
ON CONFLICT (id) DO NOTHING;

If a student with id = 1 already exists, this statement does nothing instead of throwing an error.

Update on Conflict (Upsert)

INSERT INTO students (id, name, age)
VALUES (1, 'Alice Updated', 23)
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name, age = EXCLUDED.age;

Here, if a conflict occurs on the id column, the existing row gets updated with the new values. EXCLUDED refers to the row that was attempted to be inserted. This pattern is called an upsert — a combination of insert and update.

Common Mistakes to Avoid

Mismatched Column Count and Values

-- This will cause an error
INSERT INTO students (name, age)
VALUES ('Grace');

Two columns are listed but only one value is provided. PostgreSQL will reject this with an error.

Wrong Data Type

-- This will cause an error
INSERT INTO students (name, age)
VALUES ('Henry', 'twenty');

The age column is an integer, so passing a text string like 'twenty' will fail.

Example: Complete Insert Workflow

-- Create the table
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    author VARCHAR(100),
    published_year INT
);

-- Insert a single book
INSERT INTO books (title, author, published_year)
VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', 1925);

-- Insert multiple books
INSERT INTO books (title, author, published_year)
VALUES
    ('To Kill a Mockingbird', 'Harper Lee', 1960),
    ('1984', 'George Orwell', 1949);

-- Retrieve the inserted rows
INSERT INTO books (title, author, published_year)
VALUES ('Brave New World', 'Aldous Huxley', 1932)
RETURNING id, title;

Key Points

The INSERT statement adds new rows to a table.
Column names and values must match in count and data type.
Multiple rows can be inserted in a single statement using comma-separated VALUES groups.
The RETURNING clause returns column values from the newly inserted row.
INSERT ... SELECT copies data from one table into another.
ON CONFLICT handles duplicate key errors gracefully, either by skipping or updating the conflicting row.

Previous lessons

Back to courses

Next lessons