In PostgreSQL, bulk data can be inserted into a table using an INSERT INTO statement or COPY command. In Postgres, the COPY command allows us to load bulk data from one or more files. While the multi-value INSERT command allows us to insert bulk data into a Postgres table in one go.

This blog post will show you how to insert bulk data in Postgres using INSERT and COPY commands. So, let’s begin!

How to Insert Bulk Data in Postgres Using INSERT Statement?

The comma-separated syntax must be used to insert bulk data using a single INSERT statement:

INSERT INTO table_name (column_list) 
VALUES
(value_list_1),
(value_list_2),
(value_list_3),
...
(value_list_n);

Here, in the above syntax, the “table_name” represents a table where data will be inserted. The “column_list” represents the targeted columns, while “vlaue_list_n” represents the “n” number of values to be inserted into the selected table.

Example 1: How Do I Insert Bulk Data Using INSERT Statement?

Let’s execute the SELECT statement to see the structure of an already created table named “shorlisted_students”:

SELECT * FROM shortlisted_students;

The output snippet shows that the “shortlisted_students” table has three columns: student_id, student_name, and student_email. Let’s learn how to insert the bulk data into the targeted table via the INSERT statement:

INSERT INTO shortlisted_students(student_id, student_name, student_email) 
VALUES
(1, 'Joe', 'joe@abc.com'),
(2, 'Joseph', 'joseph@abc.com'),
(3, 'Stephenie', 'stephenie@abc.com'),
(4, 'Natie', 'natie@abc.com'),
(5, 'Shaun', 'shaun@abc.com'),
(6, 'John', 'john@abc.com'),
(7, 'Anna', 'joe@abc.com'),
(8, 'Nataliya', 'nataliya@abc.com'),
(9, 'Sasha', 'sasha@abc.com'),
(10, 'Ambrose', 'ambrose@abc.com');

The bulk of ten records has been inserted into the “shortlisted_students” table successfully. Let’s verify the bulk insertion via the below-stated command:

SELECT * FROM shortlisted _students;

In this example, we inserted only ten records; however, you can insert as many records as you want using a single INSERT statement.

How to Insert Bulk Data in Postgres Using COPY Command?

To insert the bulk data via COPY command, users must follow the below syntax:

COPY table_name [(column_list)]
FROM 'file_name| file_path' 
CSV HEADER;

The above snippet will copy the bulk data from the CSV file to the Postgres table.

Example # How Do I Copy Bulk Data Using COPY Command?

Let’s execute the SELECT statement to see the structure of an already created table named “staff_info”:

SELECT * FROM staff_info;

The above snippet shows that the staff_info table has three columns. Suppose we want to insert bulk data from a CSV file into a Postgres table. The CSV file contains the following data:

Inserting every single row one by one will be a time taking process. So, to save time, we will execute the COPY command as follows:

COPY staff_info FROM 'C:\Windows\Temp\students_info.csv' CSV HEADER;

The output snippet indicates that the copy command executed successfully. To verify the bulk insertion, you can execute the following query:

SELECT * FROM staff_info;

From the output snippet, you can clearly observe that the bulk data has been inserted/copied into the staff_info table successfully.

Conclusion

In PostgreSQL, bulk data can be inserted into a table using an INSERT INTO statement or COPY command. The COPY command in Postgres lets us load bulk data from single or multiple files. Also, we can use the multi-value INSERT command to insert bulk data into a Postgres table in one go. For this purpose, the INSERT statement is used with the comma-separated syntax in Postgres. This blog post explained how to insert bulk data in Postgres using INSERT and COPY commands.