Inserting data into tables is a very common operation in any database management system. In PostgreSQL, the INSERT INTO statement is used to insert the data into any specific table. Using the INSERT statement, users can insert as many records as they want using the comma-separated syntax.

But what if a user wants to insert only those records that do not already exist in the selected table? How does Postgres deal with such situations? Well! In PostgreSQL, the INSERT statement doesn’t support the “IF NOT EXISTS” option. So alternatively, you can use the subquery to check the existence of a specific record in a table. So, let’s start!

How to Use Subquery to Insert Non-existing Values in a Table?

The below syntax will guide you on how to achieve the functionality of the “IF NOT EXISTS” option using a subquery:

INSERT INTO tab_name(col_list)
SELECT val_list
WHERE
NOT EXISTS (
SELECT col_name FROM tab_name WHERE condition
);

In the above syntax, the NOT EXIST operator/clause is used within the WHERE Clause, which will check the existence of a record. If the record to be inserted doesn’t exist in the selected table, then the sub-query will retrieve “True”. In such a case, the INSERT INTO statement will execute, and the non-existing record will be inserted into the specified table.

Example 1: Creating a Sample Table

Let’s create a sample table via CREATE TABLE command:

CREATE TABLE emp_tab(
emp_id INT,
emp_name TEXT,
emp_email VARCHAR(30)
);

In this example, we created a table named emp_tab that consists of three columns. The emp_id will take a numeric value, while emp_name and emp_email will take character values:

The resultant message verifies the working of CREATE TABLE statements. You can verify the table’s creation via the SELECT command:

SELECT * FROM emp_tab;

The emp_tab has been created successfully.

Example 2: Inserting Some Records

Let’s insert some data into the emp_tab using the INSERT query:

INSERT INTO emp_tab(emp_id, emp_name, emp_email)
VALUES (1, 'Joseph', 'joseph@abc.com'),
(2, 'Tim', 'tim@abc.com'),
(3, 'Anna', 'anna@abc.com'),
(4, 'Henry', 'henry@abc.com');

The output proves that four records have been inserted into the “emp_tab” successfully. You can verify the newly inserted data using the below-mentioned command:

SELECT * FROM emp_tab;

The output shows that four records have been inserted into the emp_tab successfully.

Example 3: Inserting Non-Existing Records

Let’s execute the below query to insert non-existing records in the emp_tab:

INSERT INTO emp_tab(emp_id, emp_name, emp_email)
SELECT 3, 'Seth', 'seth@abc.com'
WHERE
NOT EXISTS (
SELECT emp_id FROM emp_tab WHERE emp_id = 3
);

Let’s run the SELECT statement to see if the selected record has been inserted into the emp_tab or not:

SELECT * FROM emp_tab;

The output proves that the “emp_id = 3” already exists in the “emp_tab”, so the INSERT command didn’t insert that record into the targeted table. Let’s execute the INSERT command one more time to insert a non-existing record into the “emp_tab” table:

INSERT INTO emp_tab(emp_id, emp_name, emp_email)
SELECT 5, 'Seth', 'seth@abc.com'
WHERE
NOT EXISTS (
SELECT emp_id FROM emp_tab WHERE emp_id = 5
);

The output proves that the non-existing record has been inserted into the emp_tab successfully. You can verify the inserted that using the following command:

SELECT * FROM emp_tab;

This is how you can achieve the functionality of INSERT IF NOT EXISTS in PostgreSQL.

Conclusion

PostgreSQL doesn’t offer the “IF NOT EXISTS” option for the INSERT INTO statement. So alternatively, you can use the subquery to check the existence of a specific record in a table. For this purpose, use the NOT EXISTS option within the WHERE Clause. If the given record doesn’t exist in the selected table, then the sub-query will retrieve “True”. In such a case, the INSERT INTO statement will execute, and the non-existing record will be inserted into the specified table. This blog post explained how to insert non-existing records into a table using the subquery.