Guide to Using INSERT INTO in PostgreSQL

PostgreSQL: INSERT INTO Statement

The INSERT INTO statement in PostgreSQL is used to add new rows of data into a specified table. It’s one of the most commonly used commands in SQL, allowing you to insert a single row, multiple rows, or even data from another table using a subquery.

Syntax:

To insert a single row of data:

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

Here:

• table_name is the name of the table where the data will be inserted.
• Columns are specified in parentheses after the table name.
• VALUES is followed by the data you want to insert, which corresponds to each column specified.

Insert Multiple Rows:

To insert multiple rows of data in one statement:

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

Insert Using a Subquery

You can also insert data from another table using a SELECT query:

INSERT INTO table_name (column1, column2, ...)
SELECT other_column1, other_column2, ...
FROM other_table
WHERE condition;

Example 1: Basic Single Row insert

Code:

-- Insert a new product into the "products" table
INSERT INTO products (product_id, product_name, price)
VALUES (1, 'Laptop', 1000);

Explanation:

• This statement inserts a row into the products table with product_id of 1, a product_name of 'Laptop', and a price of 1000.

Example 2: Multiple Rows insert

Code:

-- Insert multiple rows into the "customers" table
INSERT INTO customers (customer_id, customer_name, country)
VALUES 
    (1, 'Alice', 'USA'),
    (2, 'Bob', 'Canada'),
    (3, 'Charlie', 'UK');

Explanation:

• Here, we insert three customers into the customers table in a single INSERT INTO statement.

Example 3: Insert Data Using a Subquery

Code:

-- Insert data from one table into another using a SELECT query
INSERT INTO high_value_orders (order_id, customer_id, total)
SELECT order_id, customer_id, total
FROM orders
WHERE total > 10000;

Explanation:

• This example inserts rows into the high_value_orders table by selecting order_id, customer_id, and total columns from the orders table, but only for orders with a total greater than 10,000.

Important Notes:

1. Auto-Increment Columns:

• If your table has an auto-incrementing column (like id), you don’t need to specify it in your insert statement. PostgreSQL will automatically assign a unique value for each new row.

2. Default Values:

• You can use DEFAULT to set a column to its default value if you don't want to specify it. For example:

Code:

INSERT INTO employees (employee_id, employee_name, salary) 
VALUES (DEFAULT, ' Encarni Ovide’, 50000);

3. Error Handling with ON CONFLICT:

• To handle duplicate values or conflicts, you can use ON CONFLICT. This is particularly helpful when you want to update an existing row if there’s a conflict.

Code:

INSERT INTO products (product_id, product_name, price)
VALUES (1, 'Tablet', 500)
ON CONFLICT (product_id) DO UPDATE SET price = 500;

Summary:

The INSERT INTO statement in PostgreSQL is essential for adding data to tables, whether inserting a single row, multiple rows, or even data from other tables. Understanding how to use INSERT INTO effectively helps streamline database operations and manage data insertion workflows.

All PostgreSQL Questions, Answers, and Code Snippets Collection.