PostgreSQL – Create Table If Not Exists

PostgreSQL – Create Table If Not Exists

In PostgreSQL, the CREATE TABLE IF NOT EXISTS statement is used to create a new table only if a table with the same name does not already exist. This helps to avoid errors when attempting to create a table that is already present in the database.

In this tutorial, we will explain the usage of CREATE TABLE IF NOT EXISTS with practical examples.

---

Basic Syntax

CREATE TABLE IF NOT EXISTS table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

The CREATE TABLE IF NOT EXISTS command first checks if the table exists in the database. If it does not, it creates the table; otherwise, the command is ignored.

---

Example 1: Create a Simple Table

Let’s create a table named students to store student information.

CREATE TABLE IF NOT EXISTS students (
    student_id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age > 0),
    grade CHAR(1)
);

Explanation:

• If the students table does not exist, this command will create it.
• If the table already exists, the command is ignored, and no error is raised.

Let us try to rerun the same query. Since, the table has already been created in the previous query, you would get a NOTICE that the students table already exists, and skips creating table.

---

Handling Errors

If you attempt to create a table without using IF NOT EXISTS and the table already exists, PostgreSQL will throw an error:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

Error Message: ERROR: relation "students" already exists

To avoid this, always use IF NOT EXISTS when you want to ensure the table is only created if it does not already exist.

---

Conclusion

The CREATE TABLE IF NOT EXISTS statement in PostgreSQL is a safe and convenient way to create tables without risking errors if the table already exists.