CREATE TABLE is a PostgreSQL's command for creating tables. In Postgres, attempting to create a table that already exists will result in an error stating that the "relation already exists". To avoid such errors, the IF NOT EXISTS clause can be used with the CREATE TABLE command.

This write-up will show you how to use the IF NOT EXISTS clause with the CREATE TABLE command. So, let’s start.

How to Create a Table in Postgres?

In Postgres, the CREATE TABLE command assists us in creating a new table. Here is the very simple yet effective syntax to create a table in PostgreSQL:

CREATE TABLE tab_name(
first_col data_type,
second_col data_type,
third_col data_type,
.....
nth_col data_type,
);

Here, tab_name represents a table to be created. first_col, second_col, …, nth_col are the column names to be defined in the desired table with their respective data types.

Example #1: Create a Table

Let’s create a new table named “emp_record”:

CREATE TABLE emp_record(
emp_id INT PRIMARY KEY,
emp_name TEXT, 
emp_leaves INT,
emp_salary INT);

In this example, we created four columns: emp_id, emp_name, emp_leaves, and emp_salaray. The emp_name column has a TEXT data type, while the remaining three columns have an INT data type:

The emp_record table with four columns has been created successfully. Now, you can insert as many records as you want. Let’s insert the following three records into the newly created table:

INSERT INTO emp_record(
emp_id, emp_name, emp_leaves, emp_salary)
VALUES (1, 'Joe', 1, 40000),
(2, 'Seth', 0, 50000),
(3, 'John', 2, 45000);

Three new records have been inserted into the emp_record table.

PostgreSQL Create Table IF NOT EXISTS

If a database already has a table with the same name, then a "relation already exists" error will appear in Postgres. To avoid such a situation, PostgreSQL provides an IF NOT EXISTS clause that can be used with the CREATE TABLE command as follows:

CREATE TABLE IF NOT EXISTS tab_name(
first_col data_type,
second_col data_type,
third_col data_type,
.....
nth_col data_type
);

Here, the IF NOT EXISTS clause will first check the existence of the targeted table. If the table already exists, then a notice will be issued instead of throwing an error. However, a new table with the specified name will be created in the database if the desired table doesn’t exist.

Example #1: What is the Need For IF NOT EXISTS Clause?

Let’s create a new table with the same name, i.e., emp_record:

CREATE TABLE emp_record(
emp_name TEXT,
emp_age INT);

Postgres throws an error stating that the targeted table already exists in the database.

Example #2: How Does IF NOT EXISTS Clause Work in Postgres?

From the previous example, it is clear that Postgres will throw an error while creating an existing table. This example will demonstrate how to avoid the “relation already exists” error:

CREATE TABLE IF NOT EXISTS emp_record(
emp_name TEXT,
emp_age INT);

The output clarifies that the IF NOT EXISTS clause shows a notice instead of throwing an error. In this way, you can use the IF NOT EXISTS clause with the CREATE TABLE command to avoid the “relation already exists” error.

Conclusion

In PostgreSQL, the CREATE TABLE command assists us in creating a new table. In Postgres, attempting to create a table that already exists will result in an error stating that the "relation already exists". To avoid such errors, the IF NOT EXISTS clause is used with the CREATE TABLE command. This article used various examples to demonstrate how the PostgreSQL CREATE TABLE IF NOT EXISTS command works in PostgreSQL.