Basic Syntax Math Functions Date Functions JSON Functions Databases Tables & Schema Management String Functions Triggers Indexes

PostgreSQL INSERT

The `INSERT` statement in PostgreSQL is used to add new rows of data into a specified table. It is a fundamental operation for populating and updating databases with new information.

Usage

The basic syntax of the `INSERT` statement is used when you need to add new records to a table. It specifies the table name, the columns to insert data into, and the corresponding values.

sql
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
提供：

In this syntax, `table_name` is the target table, `column1, column2, ...` are the columns to receive the data, and `value1, value2, ...` are the actual data values to be inserted.

Examples

1. Basic Insert

sql
INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', '[email protected]');
提供：

This example inserts a single row into the `employees` table with values for `first_name`, `last_name`, and `email`.

2. Insert Multiple Rows

sql
INSERT INTO products (product_name, price)
VALUES 
('Product A', 10.99),
('Product B', 15.49);
提供：

Here, two rows are inserted into the `products` table in one statement, each with a `product_name` and `price`.

3. Insert with Subquery

sql
INSERT INTO sales (product_id, quantity)
SELECT product_id, 5
FROM products
WHERE product_name = 'Product C';
提供：

This example inserts data into the `sales` table by selecting `product_id` from the `products` table where the `product_name` is 'Product C', setting the `quantity` to 5.

4. Handling Duplicates with ON CONFLICT

sql
INSERT INTO users (username, email)
VALUES ('jane_doe', '[email protected]')
ON CONFLICT (username) DO UPDATE 
SET email = EXCLUDED.email;
提供：

This example demonstrates handling potential duplicates by updating the `email` if the `username` already exists in the `users` table.

5. Using RETURNING Clause

sql
INSERT INTO orders (customer_id, order_date)
VALUES (123, '2023-10-11')
RETURNING order_id;
提供：

The `RETURNING` clause is used to return the `order_id` of the newly inserted row, which is especially useful with auto-increment fields.

Tips and Best Practices

Specify columns explicitly. Always list the columns in an `INSERT` statement to ensure clarity and maintain compatibility if the table structure changes.
Validate data types. Ensure that the values being inserted match the data types of the columns.

Use transactions for bulk inserts. Wrap multiple `INSERT` operations in a transaction to ensure atomicity and improve performance. For example:

sql
        BEGIN;
        INSERT INTO products (product_name, price) VALUES ('Product C', 12.99);
        INSERT INTO products (product_name, price) VALUES ('Product D', 18.49);
        COMMIT;
        提供：

Handle duplicates. Use `ON CONFLICT` for dealing with potential key conflicts, not `INSERT ... ON DUPLICATE KEY UPDATE`, which is MySQL syntax.
Leverage subqueries wisely. Utilize subqueries for dynamic data insertion from other tables to maintain data integrity and consistency.

PostqreSQL