The INSERT statement in PostgreSQL is used to add one or more new rows to a table. It allows you to insert values into all columns or only selected columns. PostgreSQL also supports inserting multiple rows in a single statement.
Syntax
INSERT INTO table_name (column_1, column_2, column_3, ...)
VALUES (value_1, value_2, value_3, ...);
Where:
- table_name: The name of the table where the data will be inserted.
- column_1, column_2, ...: The columns that will receive the values.
- value_1, value_2, ...: The values to be inserted into the corresponding columns.
Examples
Firstly, create an employee table and insert some records into it.
CREATE TABLE employee (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
age INT,
salary NUMERIC(10,2)
);
Example 1: Insert a Single Row
The following query inserts a single record into the employee table.
Query:
INSERT INTO employee (employee_id, employee_name, age, salary)
VALUES (101, 'John', 28, 50000);
Output:
Example 2: Insert Multiple Rows
The following query inserts multiple records into the employee table using a single INSERT statement.
Query:
INSERT INTO employee (employee_id, employee_name, age, salary)
VALUES
(102, 'Emma', 32, 65000),
(103, 'David', 29, 55000),
(104, 'Sophia', 35, 70000);
Output:
Example 3: Insert Values into Selected Columns
The following query inserts values only into the specified columns.
Query:
INSERT INTO employee (employee_id, employee_name)
VALUES (105, 'Michael');
Output:
Troubleshooting INSERT Statement Issues
- Duplicate primary key: Ensure the primary key value is unique.
- Column count mismatch: The number of values must match the specified columns.
- Data type mismatch: Insert values that are compatible with the column data types.
- NOT NULL constraint violation: Provide values for columns defined with the NOT NULL constraint.