PostgreSQL - Insert multiple rows

To insert multiple rows in PostgreSQL, you can use a single INSERT INTO statement by separating the values for each row with a comma. Here's a basic syntax for inserting multiple rows:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES
    (value1_row1, value2_row1, value3_row1, ...),
    (value1_row2, value2_row2, value3_row2, ...),
    ...
    (value1_rowN, value2_rowN, value3_rowN, ...);

Here's an example to illustrate this:

Assuming we have a table named students with columns id, first_name, and last_name, we can insert multiple rows as follows:

INSERT INTO students (id, first_name, last_name)
VALUES
    (1, 'John', 'Doe'),
    (2, 'Jane', 'Smith'),
    (3, 'Mike', 'Johnson');

This will insert three rows into the students table.

Additionally, you can use the following approaches to insert multiple rows:

Using a subquery: If you have data in another table that you want to insert into the target table, you can use a subquery.
```
INSERT INTO target_table (col1, col2, ...)
SELECT col1, col2, ...
FROM source_table
WHERE some_conditions;
```

Using data-modifying CTEs: Common Table Expressions can be combined with the INSERT command to modify and insert multiple rows.

WITH some_cte AS (
    -- Some CTE logic here
)
INSERT INTO target_table (col1, col2, ...)
SELECT col1, col2, ...
FROM some_cte;

Remember, when inserting multiple rows, ensure that you adhere to the constraints defined on the table (like unique constraints, foreign key constraints, etc.) to avoid conflicts and errors.

Inserting multiple rows with VALUES clause in PostgreSQL:
- Use the VALUES clause to insert multiple rows in a single INSERT statement.
```
INSERT INTO table_name (column1, column2) VALUES (value1a, value2a), (value1b, value2b), ...;
```
Using the INSERT INTO...VALUES statement for multiple rows in PostgreSQL:
- A more explicit syntax for inserting multiple rows with the VALUES clause.
```
INSERT INTO table_name (column1, column2)
VALUES
   (value1a, value2a),
   (value1b, value2b),
   ...;
```
Bulk insert of multiple rows with a single INSERT statement in PostgreSQL:
- Achieve efficiency with a single INSERT statement for multiple rows.
```
INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4), ...;
```

INSERT multiple rows from another table in PostgreSQL:

Populate multiple rows from a source table to a target table.

INSERT INTO target_table (column1, column2)
SELECT source_column1, source_column2 FROM source_table WHERE condition;

Inserting multiple rows with a SELECT subquery in PostgreSQL:
- Use a subquery in the SELECT statement to insert multiple rows.
```
INSERT INTO table_name (column1, column2) SELECT sub_column1, sub_column2 FROM sub_table WHERE condition;
```

Inserting multiple rows with array literals in PostgreSQL:

Insert multiple rows using array literals.

INSERT INTO table_name (column1, column2) VALUES
(ARRAY[value1a, value2a]), (ARRAY[value1b, value2b]), ...;

Using the COPY command for bulk insert of multiple rows in PostgreSQL:
- Leverage the COPY command for fast bulk inserts from external sources.
```
COPY table_name FROM '/path/to/data.csv' DELIMITER ',' CSV HEADER;
```
Conditional insert of multiple rows in PostgreSQL:
- Insert rows based on a condition using INSERT INTO...SELECT.
```
INSERT INTO table_name (column1, column2)
SELECT value1, value2 FROM source_table WHERE condition;
```
Inserting multiple rows with different values in PostgreSQL:
- Specify different values for each row in the VALUES clause.
```
INSERT INTO table_name (column1, column2)
VALUES (value1a, value2a), (value1b, value2b), ...;
```
Inserting multiple rows with SERIAL or Identity columns in PostgreSQL:
- Insert rows into tables with auto-incrementing columns.
```
INSERT INTO table_name (column1, auto_increment_column) VALUES (value1, DEFAULT), (value2, DEFAULT), ...;
```
Inserting multiple rows with foreign key constraints in PostgreSQL:
- Ensure foreign key constraints are met when inserting multiple rows.
```
INSERT INTO child_table (parent_id, column2) VALUES (existing_parent_id, value2), ...;
```
Inserting multiple rows with primary key constraints in PostgreSQL:
- Ensure primary key constraints are satisfied.
```
INSERT INTO table_name (primary_key_column, column2) VALUES (unique_value1, value2), ...;
```
Inserting multiple rows with unique constraints in PostgreSQL:
- Ensure unique constraints are satisfied.
```
INSERT INTO table_name (unique_column, column2) VALUES (unique_value1, value2), ...;
```
Inserting multiple rows with CHECK constraints in PostgreSQL:
- Ensure CHECK constraints are satisfied.
```
INSERT INTO table_name (column1) VALUES (value1) WHERE value1 > 0, ...;
```
Error handling and rollback for multiple-row inserts in PostgreSQL:
- Implement error handling and rollback strategies to maintain data integrity.
```
BEGIN;
-- Insert statements
COMMIT; -- or ROLLBACK on error
```