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

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:

Inserting the multiple records using a sub-select

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

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.

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.javatpoint.com/postgresql-create-table

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

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:

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

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:

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:

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:

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.

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:

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:

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:

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:

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:

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:

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.

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 javatpoint database with the help of the below command:

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

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

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

Output

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