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.
- 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.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/PostgreSQL/snippets/postgresql-insert-into.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics