Inserting Multiple Rows into Table in PostgreSQL

In PostgreSQL, you can insert multiple rows into a table in a single query, which is more efficient than executing multiple single-row INSERT statements. This feature is useful for inserting bulk data quickly and maintaining the readability of SQL scripts.

In this tutorial, we’ll explore how to insert multiple rows into a table in PostgreSQL, with practical examples and scenarios for using this feature.

Syntax for Inserting Multiple Rows

The basic syntax for inserting multiple rows is as follows:

</>
Copy
INSERT INTO table_name (column1, column2, ...)
VALUES 
    (value1a, value1b, ...),
    (value2a, value2b, ...),
    (value3a, value3b, ...);

Here, each set of parentheses represents a single row, and multiple rows can be specified within the same query.

Example 1: Basic Multiple Row Insert

Let’s create a table named students and insert multiple rows of data in a single query.

Create the table:

</>
Copy
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    grade VARCHAR(10)
);
PostgreSQL - CREATE TABLE students

Insert multiple rows into the table:

</>
Copy
INSERT INTO students (student_name, age, grade)
VALUES 
    ('Alice', 14, 'A'),
    ('Bob', 15, 'B'),
    ('Charlie', 16, 'A'),
    ('David', 14, 'C');
PostgreSQL - Insert Multiple Rows into Table

Query the table to verify the insertion:

</>
Copy
SELECT * FROM students;
PostgreSQL - SELECT FROM students

Explanation: The INSERT statement adds multiple rows to the students table in a single query. Each row specifies values for student_name, age, and grade. The student_id column is automatically populated due to the SERIAL type.

Example 2: Insert Multiple Rows with Specific Columns

It’s not necessary to specify all columns in the INSERT statement. Columns not included in the query will use their default values or NULL if no default is specified.

</>
Copy
INSERT INTO students (student_name, age)
VALUES 
    ('Eve', 13),
    ('Frank', 15);
PostgreSQL - Insert Multiple Rows into Table with Specific Columns

Query the table:

</>
Copy
SELECT * FROM students;
PostgreSQL - SELECT FROM students

Explanation: The grade column is not specified in the INSERT query, so it defaults to null for the newly added rows.

Conclusion

In PostgreSQL, inserting multiple rows into a table in a single query is both efficient and easy to implement, and we have seen a couple of examples for the same.