Перейти к основному содержимому

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.