Summary: in this tutorial, you will learn how to use the PostgreSQL INSERT
statement to insert multiple rows into a table.
To insert multiple rows into a table using a single INSERT
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:
INSERT INTO
keywords.VALUES
keyword.To insert multiple rows and return the inserted rows, you add the RETURNING
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:
INSERT
statement is atomic, meaning that either all rows are inserted, or none are. This ensures data consistency.Let's take some examples of inserting multiple rows into a table.
The following statement creates a new table called contacts
that has four columns id
, first_name
, last_name
, and email
:
CREATE TABLE contacts (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(384) NOT NULL UNIQUE
);
The following statement uses the INSERT
statement to insert three rows into the contacts
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 the INSERT
statement to insert two rows into the contacts
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 the id
column in the RETURNING
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
INSERT
statement to insert multiple rows into a table.RETURNING
clause to return the inserted rows.RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4