PostgreSQL – INSERT

PostgreSQL INSERT statement is one of the fundamental SQL commands used to add new rows to a specified table within a PostgreSQL database. This command allows users to insert data efficiently, whether for a single record or multiple records at once.

With the PostgreSQL INSERT INTO clause, we can specify the table and the corresponding columns to which the data will be added. By understanding the PostgreSQL INSERT syntax, users can easily manage data entry tasks. In this article, We will cover how to insert data into a PostgreSQL table and explain examples like inserting single and multiple rows.

PostgreSQL INSERT Statement

The PostgreSQL INSERT statement is a fundamental SQL command used to add new rows of data into a specified table within a PostgreSQL database.

It allows us to add one or more values into the table, whether it’s for a single record or multiple records.

Syntax:

The basic Syntax of the INSERT Statement is:

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

Explanation:

• table_name is the name of the table where you want to insert data.
• The columns (column1, column2, ...) specify which columns will receive data.
• The VALUES keyword is followed by the list of values that correspond to the columns.

Rules for Using the PostgreSQL INSERT Statement:

The below rules must be followed while using the PostgreSQL INSERT statement:

• Specify the Table Name: Start by specifying the table name into which you want to insert data.
• List Columns: Follow the table name with a list of columns where data will be inserted, enclosed in parentheses, and separated by commas.
• Provide Values: After the VALUES keyword, list the values to be inserted. Ensure the values are in the same order as the columns listed earlier and match the data types of the respective columns.

Examples of PostgreSQL INSERT Statement

Let’s set up a sample database and table for the demonstration of INSERT statement.

-- Create the company database
CREATE DATABASE company;

-- Connect to the company database
\c company;

-- Create the employee table
CREATE TABLE employee (
    employee_id INT PRIMARY KEY,         -- Primary Key for employee
    first_name VARCHAR(255) NOT NULL,    -- Employee's first name (mandatory)
    last_name VARCHAR(255) NOT NULL,     -- Employee's last name (mandatory)
    manager_id INT,                      -- Manager ID (optional)
    FOREIGN KEY (manager_id)             -- Self-referencing Foreign Key
        REFERENCES employee (employee_id) 
        ON DELETE CASCADE                -- Delete employee if manager is deleted
);

Look at some of the examples of Insert Statement in PostgreSQL to better understand the concept.

Example 1: PostgreSQL insert multiple rows

Here we will add some employee data to the table using the below command.

Query:

INSERT INTO employee (
    employee_id,
    first_name,
    last_name,
    manager_id
)
VALUES
    (1, 'Sandeep', 'Jain', NULL),
    (2, 'Abhishek ', 'Kelenia', 1),
    (3, 'Harsh', 'Aggarwal', 1),
    (4, 'Raju', 'Kumar', 2),
    (5, 'Nikhil', 'Aggarwal', 2),
    (6, 'Anshul', 'Aggarwal', 2),
    (7, 'Virat', 'Kohli', 3),
    (8, 'Rohit', 'Sharma', 3);

Output:

Explanation: After executing the above command, the data will be inserted into the employee table.

The overall hierarchy looks like the below image:

Example 2: Inserting a Single Row

In the above example we inserted multiple rows to the company database, whereas in this example we will add a single row to the database.

INSERT INTO employee (
    employee_id,
    first_name,
    last_name,
    manager_id
)
VALUES
    (9, 'Mohit', 'Verma', 3);
SELECT * FROM employee;

Now check for the newly added employee using the below command:

Output:

Explanation: The data for the new employee will be inserted into the ‘employee' table.

Now the overall hierarchy looks like the below image:

Important Points About PostgreSQL INSERT Statement

• You can insert multiple rows in a single INSERT statement, which can significantly improve performance compared to inserting rows one by one.
• The ON CONFLICT clause allows you to handle conflicts when inserting data.
• PostgreSQL supports arrays, allowing you to insert array data directly into a table.
• PostgreSQL allows you to return data from the inserted rows using the RETURNING clause.

Conclusion

The PostgreSQL INSERT statement offers a robust solution for adding data to your database, whether you’re handling single entries or managing bulk inserts. With its flexible syntax, including the ability to insert multiple rows in one go, this command is key to maintaining data integrity in PostgreSQL tables. Understanding how to properly use the PostgreSQL INSERT INTO command ensures seamless integration of data, making database management tasks more efficient.

FAQs on PostgreSQL INSERT

How do I insert multiple rows using the PostgreSQL INSERT statement?

You can insert multiple rows by listing several sets of values after the VALUES clause. This improves performance compared to inserting rows one by one.

Can I specify the columns when using the PostgreSQL INSERT statement?

Yes, you can specify the columns by listing them in the INSERT INTO clause. The values provided must match the order and data type of the listed columns.

What is the basic syntax for inserting data into a PostgreSQL table?

The basic syntax involves specifying the table, columns, and values to be inserted. Ensure the values match the corresponding columns in both order and data type.

R

RajuKumar19

Follow

Improve

Article Tags :

• Databases
• PostgreSQL
• postgreSQL-basics
• postgreSQL-managing-table