PostgreSQL Insert

In this section, we are going to learn the PostgreSQL insert command and examples, insert a date using the default keyword, and insert the data from one table to another in PostgreSQL pgAdmin and SQL shell (psql).

In PostgreSQL, the INSERT command is used to insert new rows into a table. We can insert a single row or multiple row values at a time into the particular table.

Syntax for PostgreSQL Insert command

INSERT INTO TABLE_NAME 
(column1, 
column2, 
column3, ……columnN)  
VALUES (value1, value2, value3, ….. valueN);  

Inserting a single record using the default value keyword

If we insert a single record using the default values keyword, the INSERT command's syntax is as follows:

INSERT INTO table
(column1, column2, ... )
DEFAULT VALUES;

Inserting the multiple records using a sub-select

If we insert multiple records using the sub-select, the Insert command syntax is as follows:

INSERT INTO table_name
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_table
[WHERE conditions];

The below table shows the Parameters or Arguments used in the insert table syntax:

Note

• We must offer each NOT NULL column value if we are inserting the records into a table using the PostgreSQL insert command.
• If the column allows NULL values, we can ignore a column from the PostgreSQL insert command.

Output

The following table shows the output messages and their meaning:

PostgreSQL insert command

We can execute the PostgreSQL insert command in two ways:

• PostgreSQL Insert statement using UI (pgAdmin)
• PostgreSQL Insert statement using SQL shell

PostgreSQL Insert statement using UI

Let's take an example to see how to insert values in a table. Here, we have a table named Student.

Example1: VALUES keyword

For creating a PostgreSQL INSERT command to list the values, we will use the VALUES keyword.

To insert values in the Student table, we are going to follow these below steps:

Step1

• Firstly, we will select Student table and then right-click on that, then select the Script option from the given list and click on the INSERT Script option from another list as we can see in the below screenshot:

Step2

• Once we click on the Insert Script, the below window will appear on the screen:

Step3

• Now, we insert the values in the place of '?' then click on the execute/ refresh button for executing the particular command, and add the record into the Student table.

INSERT INTO myschema."Student"(
"St_id", "St_Name", "St_age", "St_address", "St_blood_group")
VALUES(101, 'John', 24, 'New York', 'A+')
(102, 'Mike', 22, 'Chicago', 'B-'),
(103, 'Emily', 24, 'Boston', 'A-'),
(104, 'James', 20, 'Philadelphia', 'O+'),
(105, 'Sophia', 21, 'New York', 'B+');

SQL Query in PgAdmin4

In the below screenshot, we can see the above command in pgAdmin4:

The table Structure/Output

After executing the Insert command, we can see the Student table's output by clicking on the view table option as we can see in the below screenshot:

Example2

Firstly, we will create one new table for understanding the usage of the insert command in PostgreSQL.

Note: We can also refer to the below link to create a new table in PostgreSQL.

https://www.TheDeveloperBlog.com/postgresql-create-table

For now, we will create a department table with the help of the below command:

CREATE TABLE department (
dept_ID serial PRIMARY KEY,
Dept_name VARCHAR (255) NOT NULL,
description VARCHAR (255),
location VARCHAR(50)
);

After executing the above command, we will get the below message that the department table has been created:

Example: Insert one-row value in a table

The below command is used to insert the dept_name, location value into the department table:

INSERT INTO department (dept_name, location)
VALUES ('RESEARCH', 'Newyork');

We can check the inserted row in the particular table using the SELECT command:

SELECT * FROM department;

Output

Once we execute the above select command, we will get the below output:

If we want to insert character data, we must enclose it in single quotes (').

For example, 'RESEARCH'.

PostgreSQL offers value for the serial column repeatedly; thus, it is not required to insert a value into the serial column.

Example: Insert the multiple rows using sub-select

Here, we will insert the various rows value using sub-select into the particular table.

For example: In the below command, we will insert the multiple rows in dept_name, location columns of the department table:

INSERT INTO department (dept_name, location)
VALUES ('ACCOUNTING', 'Boston'),
('OPERATIONS','Florida'),
('SALES','Chicago');

Once we implement the above command, we will get the below message that the three values have been inserted in the dept_name and the department table's location columns:

We can check the inserted row in the particular table with the help of SELECT command:

SELECT * FROM department;

Output

Once we implemented the above select command, we will get the below output:

Example: Insert a date into a table using Default keyword

Firstly, we will add a new column named last_update into the department table and set its default value to current_date as we can see in the below command:

ALTER TABLE department ADD COLUMN last_update Date;
ALTER TABLE department ALTER COLUMN last_update
SET DEFAULT CURRENT_DATE;

After executing the above command, the department table is altered, and the last_column has been created.

The below command is used to insert a new row to define the date into the department table.

Note: In PostgreSQL, the date format is YYYY-MM-DD.

INSERT INTO department ( Dept_name, last_update)
VALUES ('FINANCE','2020-07-02');

Output

After executing the above command, we will get the message window:

To set the default value for the date column or any other column, we can also use the DEFAULT keyword as we can see in the below command:

INSERT INTO department (Dept_name, last_update)
VALUES('HR',DEFAULT);

Output

After performing the above command, we will get the below message window that the particular values have been inserted in the department table:

To check the inserted records in the department table, we will use the SELECT command:

Select * from department;

Output

We will get the below result, after executing the above command:

Example: Insert data from another table

To insert data from another table, we will follow the below steps:

Step1

Firstly, we create another table called department_tmp, which has a similar table structure like the department table:

CREATE TABLE department_tmp (LIKE department);

The department_tmp has been created after executing the above command:

Step2

Once we create the table, we will insert the rows from the department table whose values of the date column are not NULL:

INSERT INTO department_tmp 
SELECT *
FROM
department
WHERE
last_update IS NOT NULL;

Output

After executing the above command, we will get the below message window, which displays that the particular values have been inserted successfully.

Step3

After that, we will check the insert operation using the SELECT command from the department_tmp table:

SELECT * FROM department_tmp;

Output

After executing the above command, we will get the below output:

If we want to get the last inserted dept_id from the department table, we will follow the below process:

Once we insert the new row in the insert command, we will use the RETURNING clause, a PostgreSQL extension to SQL.

The following command inserts a new row into the department table and returns the last inserted Dept_id:

INSERT INTO department (dept_name, last_update)
VALUES('IT',DEFAULT)
RETURNING Dept_id;

Output

Once we implemented the above command, we will get the Dept_id=7.

After that, we will use the Select command to check that the dept_id is correct or not.

Select * from department;

Output

Once we execute the Select command, we can see that the Once we created the customer table, we will insert a one-row into the Customer table with the help of below command:Dept_id matches the last inserted Dept_ id in the department table.

PostgreSQL Insert command Using psql

In SQL shell(psql), we will first create one table named Customer table in TheDeveloperBlog database with the help of the below command:

CREATE TABLE Customer(
Cust_Id INT PRIMARY KEY NOT NULL,
Cust_Name TEXT NOT NULL,  
Cust_Address CHAR(30), 
Cust_Age INT NOT NULL Unique
);

Once we created the customer table, we will insert a one-row into the Customer table with the help of below command:

insert into customer (Cust_Id ,cust_name, Cust_address,Cust_age)  
values(101, 'john', 'boston',22);

After that, we will insert the multiple rows in the particular table, as we can see in the below command:

INSERT INTO Customer
(Cust_Id ,cust_name, Cust_address,Cust_age) 
VALUES (102, 'mike', 'newyork',24),
(103,'emily', 'newyork',23), 
 (104, 'harvey', 'florida',26);

We will use the SELECT command to check whether the above values are inserted in the Customer table or not.

Select* from customer;

Output

After executing the above command, we will get the below output: