PostgreSQL – INSERT Multiple Rows

You can insert multiple rows into a table using the INSERT INTO statement in PostgreSQL database. There are a few different ways to accomplish this, depending on your requirements and the format of the data you want to insert. The two common methods of inserting multiple values are: using the VALUES clause and using the INSERT INTO ... SELECT statement.

Using the VALUES Clause:

You can use the VALUES clause to insert multiple rows with explicit values in a single INSERT INTO statement. Each set of values is enclosed in parentheses and separated by commas.

INSERT INTO table_name (column1, column2, column3) 
VALUES (value1_1, value1_2, value1_3), 
(value2_1, value2_2, value2_3), 
(value3_1, value3_2, value3_3);

Example:

INSERT INTO employees (first_name, last_name, salary) 
VALUES ('Alice', 'Johnson', 55000), 
('Bob', 'Smith', 60000), 
('Carol', 'Williams', 52000);

Using the INSERT INTO … SELECT Statement:

You can also insert multiple rows from the result of a SELECT query. This can be useful if you want to insert rows that are retrieved from another table.

INSERT INTO target_table (column1, column2, column3) 
SELECT source_column1, source_column2, source_column3 FROM source_table WHERE conditions;

Example:

INSERT INTO employees_audit (employee_id, action, timestamp) 
SELECT employee_id, 'INSERT', NOW() FROM employees WHERE salary > 55000;

Here, rows are inserted into the employees_audit table for employees whose salary is greater than 55000, indicating an “INSERT” action and using the current timestamp.

Both of these methods allow you to insert multiple rows in a single statement, which can be more efficient and convenient than inserting one row at a time. The data you’re inserting must match with the column data types of the target table and adhere to any constraints defined on the table.