In PostgreSQL, you can use the IF NOT EXISTS clause of the CREATE TABLE statement to check whether or not a table of the same name already exists in the database before creating it.

The table will only be created if no other table exists with the same name. If a table already exists with that name, a “notice” will be issued instead of an error.

Example

Here’s an example to demonstrate:

CREATE TABLE IF NOT EXISTS t1 (
  c1 INT,
  c2 VARCHAR(10)
);

Here, t1 is the table name, and everything between the parentheses is the table definition (i.e. columns, etc).

In that case, the table will only be created if there isn’t already one called t1.

Check that the Table Now Exists

We can query the pg_tables view to check to see if the table now exists:

SELECT EXISTS (
    SELECT FROM 
        pg_tables
    WHERE 
        schemaname = 'public' AND 
        tablename  = 't1'
    );

Result:

True

In this case I get True, which means that the table does exist and that I have access to it.

Depending on your configuration, you may get t/f instead of True/False.

Try to Create the Table Again

If we attempt to create that table again:

CREATE TABLE IF NOT EXISTS t1 (
  c1 INT,
  c2 VARCHAR(10)
);

We don’t get an error, we get a notice:

NOTICE:  relation "t1" already exists, skipping

As expected, the notice tells us that the table already exists.

Without the IF NOT EXISTS Clause

Here’s what happens when we don’t use the IF NOT EXISTS clause when trying to create a table that already exists:

CREATE TABLE t1 (
  c1 INT,
  c2 VARCHAR(10)
);

This time we get an error:

ERROR:  relation "t1" already exists

Note that the IF NOT EXISTS clause does not check the table structure/definition. It simply checks that there’s no existing table with the same name that we’re trying to give to the table that we’re creating.

In other words, just because a table of that name already exists, it doesn’t mean it has the correct definition.

The IF NOT EXISTS functionality was added in PostgreSQL 9.1 (release notes).