Inserting multiple rows into a table

To insert multiple rows into a table using a singleINSERT statement, you use the following syntax:

INSERT INTO table_name (column_list)VALUES    (value_list_1),    (value_list_2),    ...    (value_list_n);

In this syntax:

• First, specify the name of the table that you want to insert data after theINSERT INTO keywords.
• Second, list the required columns or all columns of the table in parentheses that follow the table name.
• Third, supply a comma-separated list of rows after theVALUES keyword.

To insert multiple rows and return the inserted rows, you add theRETURNING clause as follows:

INSERT INTO table_name (column_list)VALUES    (value_list_1),    (value_list_2),    ...    (value_list_n)RETURNING* | output_expression;

Inserting multiple rows at once has advantages over inserting one row at a time:

• Performance: Inserting multiple rows in a single statement is often more efficient than multiple individual inserts because it reduces the number of round-trips between the application and the PostgreSQL server.
• Atomicity: The entireINSERT statement is atomic, meaning that either all rows are inserted, or none are. This ensures data consistency.

Inserting multiple rows into a table examples

Let's take some examples of inserting multiple rows into a table.

Setting up a sample table

The following statementcreates a new table calledcontacts that has four columnsid,first_name,last_name, andemail:

CREATE TABLE contacts (    idSERIAL PRIMARY KEY,    first_nameVARCHAR(50)NOT NULL,    last_nameVARCHAR(50)NOT NULL,    emailVARCHAR(384)NOT NULL UNIQUE);

1) Basic inserting multiple rows example

The following statement uses theINSERT statement to insert three rows into thecontacts table:

INSERT INTO contacts (first_name, last_name, email)VALUES    ('John','Doe','john.doe@example.com'),    ('Jane','Smith','jane.smith@example.com'),    ('Bob','Johnson','bob.johnson@example.com');

PostgreSQL returns the following message:

INSERT 0 3

To verify the inserts, you use the following statement:

SELECT * FROM contacts;

Output:

id | first_name | last_name |          email----+------------+-----------+-------------------------  1 | John       | Doe       | john.doe@example.com  2 | Jane       | Smith     | jane.smith@example.com  3 | Bob        | Johnson   | bob.johnson@example.com(3 rows)

2) Inserting multiple rows and returning inserted rows

The following statement uses theINSERT statement to insert two rows into thecontacts table and returns the inserted rows:

INSERT INTO contacts (first_name,last_name, email)VALUES    ('Alice', 'Johnson', 'alice.johnson@example.com'),    ('Charlie', 'Brown', 'charlie.brown@example.com')RETURNING *;

Output:

id | first_name | last_name |           email----+------------+-----------+---------------------------  4 | Alice      | Johnson   | alice.johnson@example.com  5 | Charlie    | Brown     | charlie.brown@example.com(2 rows)INSERT 0 2

If you just want to return the inserted id list, you can specify theid column in theRETURNING clause like this:

INSERT INTO contacts (first_name,last_name, email)VALUES    ('Eva', 'Williams', 'eva.williams@example.com'),    ('Michael', 'Miller', 'michael.miller@example.com'),    ('Sophie', 'Davis', 'sophie.davis@example.com')RETURNING id;

Output:

id----  6  7  8(3 rows)INSERT 0 3

Summary

• Specify multiple value lists in theINSERT statement to insert multiple rows into a table.
• UseRETURNING clause to return the inserted rows.

Last updated onJanuary 22, 2024

On this page

• Inserting multiple rows into a table
• Inserting multiple rows into a table examples
• Summary