PostgreSQL Insert Into

The INSERT INTO Statement

The INSERT INTO statement adds one or more new rows to a table. You specify the target table, the columns you want to fill, and the values to insert. Any column left out will receive its default value, or NULL if no default is defined and the column allows nulls.

INSERT INTO customers (full_name, email)
VALUES ('Ravi Kumar', 'ravi.kumar@example.com');

Inserting Multiple Rows at Once

PostgreSQL allows you to insert several rows in a single statement by separating each row's values with a comma. This is more efficient than running multiple individual INSERT statements because it reduces the number of round trips to the database.

INSERT INTO customers (full_name, email)
VALUES
    ('Anita Sharma', 'anita.sharma@example.com'),
    ('John Miller', 'john.miller@example.com'),
    ('Wei Zhang', 'wei.zhang@example.com');

Returning Inserted Data

A feature unique to PostgreSQL among many databases is the RETURNING clause, which lets an INSERT statement immediately return the values of the row it just created, such as an auto-generated ID, without a separate SELECT query.

INSERT INTO customers (full_name, email)
VALUES ('Priya Nair', 'priya.nair@example.com')
RETURNING customer_id;

• Column order in the INSERT list must match the order of the values
• Omitted columns fall back to their DEFAULT or NULL
• NOT NULL columns without a default must always be given a value
• RETURNING can return any column, or use RETURNING * for the whole row
• Wrapping multiple inserts in a transaction ensures they all succeed or all fail together