How to Insert Multiple Rows to a Table in PostgreSQL?

Inserting multiple rows into a table in PostgreSQL is a common operation that may enhance efficiency when managing large datasets. By executing a single SQL query, more than one row is inserted simultaneously, which reduces the overload of multiple individual inserts. This method not only helps in the insertion process but also enhances the database performance specifically in situations wherein large information insertion is required. In this article, we will learn about various techniques and see some examples for a clear and good understanding of how to Insert Multiple Rows into a Table in PostgreSQL .

Introduction to Insert statement in the PostgreSQL

The INSERT statement in PostgreSQL is a fundamental SQL command used to add new rows of data into a table. It allows users to insert one or multiple rows into a table with specified values for each column. Wecan insert values explicitly into specified column names to insert values in the order defined by the table schema.

Syntax:

INSERT INTO table_name (column1, column2, ...)
VALUES
    (value1_row1, value2_row1, ...),
    (value1_row2, value2_row2, ...),

To Insert Multiple Rows into a Table in PostgreSQL we will have to create a database.

Examples of Inserting Multiple Rows to a Table in PostgreSQL

Example 1

Let’s say we have a table named Students_1 with columns Student_id, Name and Marks. To insert multiple rows into this table, we can use the following command.

Query:

INSERT INTO Students_1 (Student_id, Name, Marks) 
VALUES  (1, 'John Doe', 50), 
(2, 'Jane Smith', 60),  
(3, 'Bob Johnson', 55),  
(4, 'Rahul Sharma', 56), 
(5, 'Diya Dubey', 67), 
(6, 'Divya Verma', 89),  
(7, 'Ram Kapoor', 45),
 (8, 'Raj Gupta', 88), 
(9, 'James Roy', 78),
 (10, 'Esha Verma', 90);

Output:

Students_1 Table

Explanation: As we see in the above output, the ‘INSERT INTO’ statement efficiently provides more than one rows to our ‘Student_1’ table in a single query. This approach most effective simplifies the insertion method but additionally complements database overall performance.

Example 2

Similarly as the above example, we have to first create another table to insert data into that table.

Query:

create table Students_2 ( Student_id int, Name varchar(50), Marks int);

After the table let’s say ‘Students_2’ is created with columns ‘Student_id’, ‘Name’, and ‘Marks’ again. Insert data into the table, for this example we do not have to add the column name but we have to add the data according to your column’s data type in the similar order.

Query:

INSERT INTO Students_2 
VALUES (11, 'John James', 55),
 (12, 'Sam Smith', 60), 
(13, 'Bob Roy', 55), 
(14, 'Rahul Verma', 43), 
(15, 'Priya Dubey', 67), 
(16, 'Ankit Verma', 98), 
(17, 'Ram Gupta', 88), 
(18, 'Raj Sharma', 65), 
(19, 'James Jhonson', 72),
 (20, 'Geet Sharma', 34);

Output:

Students_2

Explanation: For this example even without adding the column name the all rows are inserted in the ‘Students_2’ table within a single query. So when entering large data it is not necessary to add column but remember the data type of all your columns. Make sure the order of the values matches the order of the columns specified in the ‘INSERT INTO’ statement.

Inserting Rows from Another Table

If you want to insert multiple rows from another table, you can use the INSERT INTO … SELECT statement.

Syntax:

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;

Example

Taking the example of ‘Students_1’ and ‘Student_2’ table. Replace ‘Students_1′ with the name of the table you want to insert rows into and ‘Students_2’ with the name of the table from which you want to select the rows.

Query:

INSERT INTO Students_1 (id, name, Marks)
SELECT Students_id, name, marks
FROM Students_2
WHERE marks > 50;

Output:

Output

Explanation: This example inserts rows into the employees table by selecting rows from the ‘Students_1′ table where the ‘Marks’ is greater than 50. Remember to adjust column names, table names, and conditions based on your specific use case.

Conclusion

Overall, the INSERT statement in PostgreSQL is used for inserting new data into tables within a database. Its easy syntax and flexibility allows users to populate tables with initial data or add new records efficiently. Whether inserting values explicitly into specified columns or retrieving data from other tables or query results, the INSERT statement good for management and manipulation.