How to Insert Multiple Rows to a Table in PostgreSQL?

Inserting multiple rows into a table in PostgreSQL is a common and efficient operation, especially when handling large datasets. By executing a single SQL query, multiple rows can be added simultaneously, reducing overhead and enhancing performance. This method is particularly valuable in scenarios where bulk data insertion is required.

In this article, we will explain the INSERT statement in PostgreSQL, its syntax, and multiple techniques to insert rows into tables. We will also provide examples with outputs to ensure a clear understanding of the concept.

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. We can 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, ...),

Key Terms

• table_name: The name of the target table.
  
• column1, column2, ...: The names of the columns where values will be inserted.
  
• VALUES: Specifies the data to be inserted, with each row enclosed in parentheses and separated by commas.

Examples of Inserting Multiple Rows to a Table in PostgreSQL

The following examples demonstrate different techniques for adding multiple rows using the INSERT statement, ensuring optimized performance and clear data organization.

Example 1: Adding Multiple Rows to a Table

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

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: Adding Rows Without Specifying Column Names

Similarly as the above example, we have to first create another table named Students_2 and add multiple rows without explicitly specifying column names.

1. Create the Table

CREATE TABLE Students_2 (
    Student_id INT, 
    Name VARCHAR(50), 
    Marks INT
);

Explanation:

After 'Students_2' table 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 our column's data type in the similar order.

2. Insert Data

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

Explanation:

For this example even without adding the column name, 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 our columns. Make sure the order of the values matches the order of the columns specified in the 'INSERT INTO' statement.

Example 3: Inserting Rows from Another Table

In this example, we can insert multiple rows from one table to another table using the INSERT INTO ... SELECT statement. Replace 'Students_1' with the name of the table we want to insert rows into and 'Students_2' with the name of the table from which we 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

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 our specific use case. This approach is ideal for transferring data between tables based on specific conditions, streamlining data manipulation tasks

Conclusion

The INSERT statement in PostgreSQL provides a flexible and efficient way to add data to tables. Whether inserting explicit values, fetching rows from another table, or using a loop for dynamic operations, it ensures flexibility and performance. By Using techniques like inserting multiple rows or using transactions, users can streamline their database operations and enhance data management efficiency.