Postgres Bulk Insert

A bulk insert is a process that adds multiple rows of data to a table in a single operation. This can be much faster than inserting one row at a time, especially when dealing with large amounts of data.

The most common method of performing a bulk insert in PostgreSQL is the COPY command which can take the data from a file or standard input and insert it into a table. The COPY command requires that the data should be in a specific format, usually a CSV or text file.

Let us explore how to use this command to perform a bulk insert in PostgreSQL.

PostgreSQL Bulk Insert

Let us explore the steps to perform a bulk insert in PostgreSQL.

Prepare Your Data

Before performing a bulk insert, ensure that the target data is in the suitable format. We recommend that you configure your data in CSV or TSV. You can structure the target data in rows and columns which are separated by commas or tabs.

Connect to PostgreSQL

Next, connect to your PostgreSQL database using your desired client. For this tutorial, we use the PSQL utility for ease of use and universal access.

$ psql -U postgres -d <database_name>

For example, you can run the following query to use the user_information database:

$ psql -U postgres -d user_information

If you do not have the target database, you can create it using the CREATE DATABASE command:

CREATE DATABASE <db_name>

Create a Table

Next, we need to ensure that the table where we wish to insert the data exists. The table structure must match the data structure including the supported data types.

If the table does not exist, you can use the CREATE TABLE command:

CREATE TABLE network_users (
id SERIAL PRIMARY KEY,
username VARCHAR(255) NOT NULL,
ip_address INET,
mac_address MACADDR,
mime TEXT
);

The given command should create a table called “network_users” with the id, username, ip_address, mac_address, and mime columns.

Once the table is ready, we can load the data into the PostgreSQL table. Again, it is good to ensure that the data file is accessible from the machine where the server is running.

Execute the Bulk Insert

Next, we can use the COPY command to load the data from the file into the database table. The command syntax is as follows:

COPY table_name (column1, column2, column3)
FROM 'path/to/data_file'
WITH (FORMAT csv|text, DELIMITER 'delimiter', HEADER);

You can specify the format (CSV or text), the delimiter used in your file (e.g., ‘,’ for CSV, ‘\t’ for TSV), and whether your file includes a header row.

For example, to copy the data to the “network_users” table, we can run the command as follows:

copy network_users (id, username, ip_address, mac_address, mime) from 'network_users.
csv' with (format csv, delimiter ',', header);

This allows PostgreSQL to load the data from the file into the table. You can verify if the insert is successful by querying the data in the table.

PostgreSQL Bulk Insert PgAdmin

We can also use a graphical interface that pgAdmin provides to import a data file.

Start by launching pgAdmin and connect to your PostgreSQL database by providing the necessary connection details.

Next, locate the table where you wish to perform a bulk insert. You can find your databases and tables in the browser panel.

Right-click on the table and select “Import/Export”.

In the “Import/Export” wizard, choose the “Import” option and select the data source type. For example, you can import a query or the clipboard from a file.

In the next step, provide the file details for the bulk insert. Select the file format (CSV, TSV, to specify the path to your data file, and set the delimiter that is used in the file.