Insert Data into a Table in PostgreSQL

What is PostgreSQL INSERT Query?

When it comes to inserting data into a table in PostgreSQL, it involves adding new information to the database. This process begins by establishing a connection to the PostgreSQL database. If the table doesn't exist, it can be created with column definitions and constraints.

The Postgresql INSERT query is structured with the INSERT INTO statement, table name, and column names where data will be inserted. We can provide the values for the corresponding using the VALUES clause. We can also add multiple rows that can be inserted in one query by separating the sets of values with commas.

Syntax of PostgreSQL INSERT Query

To insert data into a table in PostgreSQL, you can use the INSERT INTO statement. The basic syntax for the INSERT query is as follows:

Where the parameters are defined as,

• table_name: The name of the table into which you want to insert data.
• column1, column2, column3, ...: The names of the columns in the table where you want to insert data.
• value1, value2, value3, ...: The corresponding values you want to insert into the respective columns.

We can insert multiple rows in a single query via multiple sets of values separated by commas:

Now, we will ensure that the number and order of values in each set match the columns specified in the INSERT INTO statement. We will also ensure that the data types and constraints are defined for each column to avoid any potential errors during insertion.

Basic Example of PostgreSQL INSERT Query

To understand the Postgresql insert query, let’s create a table employees:-

Now, let's insert a new employee into the employees table:

After executing the above query, the below output will be generated:

Output:

RETURNING Clause

The RETURNING clause in PostgreSQL insert query is a feature that allows users to retrieve specific data from rows that are affected by an INSERT, UPDATE, or DELETE operation. It enables capturing the modified data directly as part of the query result, eliminating the need for additional queries to access the affected data.

You can use the return clause in the insert statement as an optional clause to return the information of the inserted data in the PostgreSQL insert query in the table.

Now, let’s suppose you have to return the entire row, we can use the asterisk keyword (*) after the return clause.

Considering the above case, we will insert the data in the employee's table and also return the table in the console with the help of the below insert statement.

Example:

After executing the above query, the below output will be generated:

Output:

Now, let’s suppose we have to find the specific information, to do so we will specify one or more columns after the return clause.

Considering the above case, we will insert the data in the employee's table and also return the specific column in the console with the help of the below insert statement.

Example:

After executing the above query, the below output will be generated:

Output:

Now, if we have to rename the returned value we can use the AS keyword after the return clause.

Considering the above case, we will insert the data in the employee's table and also return the specific column by renaming the column in the console with the help of the below insert statement.

Example:

After executing the above query, the below output will be generated:

Output:

More Examples

Insert Multiple rows

To insert multiple rows into a table in PostgreSQL, you can use a single INSERT INTO statement with multiple sets of values specified in the VALUES clause. Each set of values corresponds to a row that you want to insert.

We will be using the above-created employee's table to insert multiple rows into the table at once as:

Example:

After executing the above query, the below output will be generated:

Output:

Inserting a single row into a table

To insert a single row into a table in PostgreSQL, you can use the INSERT INTO statement with the specified column names and their corresponding values. Here's an example:

We will be using the above-created employee's table to insert a new employee as:

Example:

In this case, We are inserting a single row using the above query. After executing the above query, the below output will be generated:

Output:

In this example, the data for the f_name, l_name, age, and dept columns are provided within the VALUES clause, and PostgreSQL will insert this single row into the employees table.

Inserting character string that contains a single quote

To insert a character string that contains a single quote into the employees table, you can use the same technique of escaping the single quote with another single quote. Here's an example:

Let's say you want to insert an employee with the last name O'Reilly into the employees table:

Example:

In this case, we are inserting a character string with a single quote. After executing the above query, the below output will be generated:

Output:

In this example, the last name O'Reilly is enclosed within single quotes, and the single quote within the last name is escaped by using two single quotes together (''). This ensures that PostgreSQL treats the single quote as part of the data and doesn't interpret it as the end of the string. The data will be inserted correctly, and the employees table will include the new row with the last name O'Reilly.

Getting the last insert Id

In the PostgreSQL insert query, you can retrieve the last inserted ID after an INSERT operation using the RETURNING clause combined with the INSERT INTO statement. To achieve this without plagiarism, here's an example of how you can get the last insert ID for the employees table:

Suppose you have an employees table with an auto-incrementing primary key column named emp_id. To insert a new employee and get the last insert ID, you can use the following query:

Example:

In this case, we can get the last insert Id by executing the above query. Below output will be generated while executing:

Output:

In this query, the RETURNING clause is added at the end of the INSERT INTO statement, specifying the emp_id column. The RETURNING clause will make the query return the emp_id value of the newly inserted row.

After executing the query, you will get the last inserted emp_id as a result. This approach allows you to efficiently retrieve the last insert ID without the need for additional queries, ensuring data accuracy and consistency in the employees table.