One unique feature of PostgreSQL is the ability to insert multiple rows at once with a single command. To insert multiple rows, you need to specify column names and provide a list of rows with comma-separated values in the INSERT INTO command. If a table has an auto-increment field, you need not specify the column. In this post, you will learn how to insert multiple rows in PostgreSQL.
Table of Contents
Syntax
Below is the syntax of the INSERT INTO command for inserting multiple rows.
INSERT INTO table_name (column1, column2, column3) VALUES
(row1_value1, row1_value2, row1_value3),
(row2_value1, row2_value2, row2_value3),
(row3_value1, row3_value2, row3_value3);After INSERT INTO, specify the table name in which you want to insert data and specify the column list. After the VALUES keyword, you need to specify all rows of data separated with a comma.
Insert Multiple Rows Example
For example, I have created an employee table. This table has three columns: ID, name, and salary.
CREATE TABLE employee
(
id int primary key,
name varchar(100),
salary numeric(10,2)
)Below is an INSERT query example of inserting multiple rows at once in a PostgreSQL table. In this query, we are inserting 20 rows at a time. We have specified a column list and a list of rows with a comma-separated list in the INSERT INTO query, as shown in the below example.
INSERT INTO employee (id, name, salary) VALUES
(1, 'John Doe' , 8000),
(2, 'Jane Smith' , 7500.5),
(3, 'Alice Williams' , 7200),
(4, 'Charlie Brown' , 8500.25),
(5, 'Bob Johnson' , 7800.5),
(6, 'Eva Davis' , 9500.5),
(7, 'Frank Miller' , 8800),
(8, 'Grace Lee' , 7600.75),
(9, 'Henry Turner' , 8200.25),
(10, 'Ivy Chen' , 7800),
(11, 'Jack Robinson' , 7100.5),
(12, 'Katie Adams' , 9200.75),
(13, 'Leo Wang' , 8900),
(14, 'Megan Taylor' , 7700.5),
(15, 'Nathan Hill' , 8100.75),
(16, 'Olivia Foster' , 9300),
(17, 'Paul White' , 7400.5),
(18, 'Quinn Taylor' , 8600.75),
(19, 'Rachel Johnson' , 7900),
(20, 'Samuel Brown' , 8800.5);
You can verify the total rows inserted in the Messages window. As shown in the above screenshot, PostgreSQL provides a total count of inserted rows.
Multiple Rows Example with Auto-increment key
The auto-increment key is used to create uniqueness in a table. For auto-increment in a table, PostgreSQL provides three special data types: smallserial, serial, and bigserial.
If you are using an auto-increment column in a table, then you do not need to specify it in the column list of the INSERT INTO query.
For example, create a new employee table with an auto-increment key.
CREATE TABLE employee
(
ID serial PRIMARY KEY,
name varchar(100),
salary numeric(10,2)
)
Now use the INSERT INTO query without specifying the ID column, as shown below.
INSERT INTO employee (name, salary) VALUES
('John Doe' , 8000),
('Jane Smith' , 7500.5),
('Alice Williams' , 7200),
('Charlie Brown' , 8500.25),
('Bob Johnson' , 7800.5),
('Eva Davis' , 9500.5),
('Frank Miller' , 8800),
('Grace Lee' , 7600.75),
('Henry Turner' , 8200.25),
('Ivy Chen' , 7800),
('Jack Robinson' , 7100.5),
('Katie Adams' , 9200.75),
('Leo Wang' , 8900),
('Megan Taylor' , 7700.5),
('Nathan Hill' , 8100.75),
('Olivia Foster' , 9300),
('Paul White' , 7400.5),
('Quinn Taylor' , 8600.75),
('Rachel Johnson' , 7900),
('Samuel Brown' , 8800.5);
Return auto-increment ID generated by Auto-increment Column
To return all auto-increment IDs generated, you need to use the RETURNING keyword along with the column list you need to return.
INSERT INTO employee (name, salary) VALUES
('John Doe' , 8000),
('Jane Smith' , 7500.5),
('Alice Williams' , 7200),
('Charlie Brown' , 8500.25)
RETURNING ID;
To return all inserted data in a return, use the * after RETURNING keyword, as shown below.
INSERT INTO employee (name, salary) VALUES
('John Doe' , 8000),
('Jane Smith' , 7500.5),
('Alice Williams' , 7200),
('Charlie Brown' , 8500.25)
RETURNING *;
Insert Multiple Rows from Select Example
Insert multiple rows from the SELECT query; use INSERT INTO without the VALUES keyword. After the column list, provide the SELECT query. Remember, the column list length in the INSERT query and the SELECT query must match as shown in below example.
INSERT INTO employee (name, salary)
SELECT name, salary FROM users;
Summary
PostgreSQL allows inserting multiple rows with one INSERT INTO command by specifying a column list and a list of rows with comma-separated values. To retrieve auto-increment IDS, use the RETURNING keyword. Multiple rows are also inserted from another table in a SELECT query.
Related Posts: