PostgreSQL Batch Data Insertion (Using COPY Command or INSERT ALL)

This article is about batch data insertion and mainly focuses on a tutorial for basic operations in PostgreSQL.

What is the COPY command?

The COPY command is used to insert large amounts of data into tables by using files or standard input. It allows you to perform bulk inserts efficiently.

Syntax for Using the COPY Command

COPY table_name (column1, column2, ...)
FROM 'file_location'
DELIMITER '{delimiter}'
CSV;

• table_name: Specifies the name of the target table where data will be inserted.
• column1, column2, ...: Specify the names of columns into which data is to be loaded.
• file_location: Specifies the location of the file (absolute path, relative path, or network path).
• delimiter: The character used as a delimiter between values; defaults to ,.

Example

COPY customers (name, email)
FROM '/customers.csv'
DELIMITER ',';

What is the INSERT ALL command?

The INSERT ALL command is another method for inserting large amounts of data into PostgreSQL. It allows you to insert multiple rows by specifying a set of table names and column lists.

Syntax for Using the INSERT ALL Command

INSERT ALL
INTO table1 (column1, column2, ...)
INTO table2 (columnA, columnB, ...)
...
SELECT * FROM source_table;

• table1, table2, ...: Specify the names of tables into which data is to be inserted.
• column1, column2, ..., columnA, columnB, ...: Specify the names of columns for each table.
• source_table: Specifies where the data comes from; it can also be a subquery within another INSERT ALL statement.

Example

INSERT ALL
INTO orders (order_id, customer_name, order_date)
INTO customers (customer_id, name, email)
SELECT order_id, customer_name, order_date FROM order_data;

Summary

The COPY command and the INSERT ALL command are two commonly used methods for batch data insertion in PostgreSQL. Understanding their syntax can help improve your work efficiency and enhance development experience.