PostgreSQL - INSERT

The INSERT statement in PostgreSQL is used to add new rows to a table. It allows you to specify values for one or more columns, and the system will then insert the new row with the provided values into the table.

Basic Syntax:

Inserting into All Columns:

INSERT INTO table_name
VALUES (value1, value2, ...);

Inserting into Specific Columns:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Inserting Multiple Rows:

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

Inserting Data from Another Table:

INSERT INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM another_table
WHERE condition;

Examples:

Basic Insert:
Let's say you have a students table and you want to add a new student:
```
INSERT INTO students (student_id, name, age)
VALUES (1, 'John Doe', 20);
```

Insert Multiple Rows:

Inserting multiple students at once:

INSERT INTO students (student_id, name, age)
VALUES 
   (2, 'Jane Smith', 21),
   (3, 'Alice Johnson', 22);

Insert Data from Another Table:
Suppose you have another table temp_students and you want to insert some students from this table into the students table:
```
INSERT INTO students (student_id, name, age)
SELECT student_id, name, age
FROM temp_students
WHERE age > 20;
```

Notes:

If a table has columns with default values or auto-incrementing sequences, and you don't specify values for those columns, then the default values or the next value from the sequence will be used.
If inserting a new row would violate a unique constraint or primary key, the insert will fail, unless you've provided an ON CONFLICT clause to handle the conflict.
The RETURNING clause can be appended to the INSERT statement to return the values of the inserted row. This can be especially useful when you have auto-incrementing primary keys or columns with default values and you want to know the values that were assigned to the new row. Example:
```
INSERT INTO students (name, age)
VALUES ('Bob', 23)
RETURNING student_id, name, age;
```
The above would insert a student and return the student_id (possibly auto-generated), name, and age of the inserted student.

PostgreSQL INSERT example:
- The INSERT statement is used to add new records to a table.
```
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
```
Inserting data into a specific column with INSERT in PostgreSQL:
- Specify columns when inserting data to insert into specific ones.
```
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
```
Inserting multiple rows with a single INSERT statement in PostgreSQL:
- Insert multiple rows with a single INSERT statement for efficiency.
```
INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4), ...;
```

INSERT with SELECT statement in PostgreSQL:

Populate a table by selecting data from another table.

INSERT INTO table_name (column1, column2) SELECT column3, column4 FROM another_table WHERE condition;

Bulk insert with COPY command in PostgreSQL:
- Use the COPY command for fast bulk inserts.
```
COPY table_name FROM '/path/to/data.csv' DELIMITER ',' CSV HEADER;
```

Handling duplicate key violations in PostgreSQL INSERT:

Handle duplicates using the ON CONFLICT clause.

INSERT INTO table_name (column1, column2) VALUES (value1, value2) ON CONFLICT (column1) DO NOTHING;

Returning values from INSERT statement in PostgreSQL:
- Retrieve values generated during the INSERT operation.
```
INSERT INTO table_name (column1, column2) VALUES (value1, value2) RETURNING column3;
```

Conditional INSERT with CASE statement in PostgreSQL:

Conditionally insert data using the CASE statement.

INSERT INTO table_name (column1, column2) VALUES (CASE WHEN condition THEN value1 ELSE value2 END, value3);

Using DEFAULT values in INSERT statement in PostgreSQL:
- Insert default values into columns.
```
INSERT INTO table_name (column1, column2) VALUES (DEFAULT, value2);
```
Inserting data into multiple tables in a single transaction in PostgreSQL:
- Use a transaction to ensure atomicity when inserting into multiple tables.
```
BEGIN;
INSERT INTO table1 (column1) VALUES (value1);
INSERT INTO table2 (column2) VALUES (value2);
COMMIT;
```
Inserting data into tables with SERIAL or Identity columns in PostgreSQL:
- Insert data into tables with auto-incrementing columns.
```
INSERT INTO table_name (column1, auto_increment_column) VALUES (value1, DEFAULT);
```
Inserting data into tables with foreign key constraints in PostgreSQL:
- Ensure foreign key constraints are satisfied when inserting data.
```
INSERT INTO child_table (parent_id, column2) VALUES (existing_parent_id, value2);
```
Inserting data into tables with primary key constraints in PostgreSQL:
- Ensure primary key constraints are satisfied.
```
INSERT INTO table_name (primary_key_column, column2) VALUES (unique_value, value2);
```
Inserting data into tables with unique constraints in PostgreSQL:
- Ensure unique constraints are satisfied.
```
INSERT INTO table_name (unique_column, column2) VALUES (unique_value, value2);
```
Inserting data into tables with CHECK constraints in PostgreSQL:
- Ensure CHECK constraints are satisfied.
```
INSERT INTO table_name (column1) VALUES (value1) WHERE value1 > 0;
```
Inserting data into tables with triggers in PostgreSQL:
- Insert data, and triggers automatically execute predefined actions.
```
CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION trigger_function();
```