PostgreSQL – INSERT

In PostgreSQL, the INSERT statement is used to add new rows to a database table. As one creates a new database, it has no data initially. PostgreSQL provides the INSERT statement to insert data into the database.

Syntax:
INSERT INTO table(column1, column2, …)
VALUES
    (value1, value2, …);

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

• First thing to do is specify the table name followed by the columns where you want to insert the data or rows.
• Secondly, you must list a comma-separated value list after the VALUES clause. The value list must be in the same order as the columns list specified after the table name.

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

• Create a database named “company” with the below command:

  CREATE DATABASE company;

• Add a table of “employee” to show the company hierarchy into the database using the below command:

  CREATE TABLE employee (
      employee_id INT PRIMARY KEY,
      first_name VARCHAR (255) NOT NULL,
      last_name VARCHAR (255) NOT NULL,
      manager_id INT,
      FOREIGN KEY (manager_id) 
      REFERENCES employee (employee_id) 
      ON DELETE CASCADE
  );

  The value in the manager_id column represents the senior manager who the employee reports to. If it’s Null, he/she doesn’t report to anyone.

Example 1:
Here we will add some employee data to the table using the below command:

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:
Use the below command to verify the inserted data:

SELECT * FROM employee;

The overall hierarchy looks like the below image:

Example 2:
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);

Output:

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

SELECT * FROM employee;

Now the overall hierarchy looks like the below image: