How to Create Table if It Does Not Exist in PostgreSQL

1. Use the CREATE TABLE Query to Create a Table if It Does Not Exist in PostgreSQL
2. Use the CREATE or REPLACE Query to Create a Table if It Does Not Exist in PostgreSQL
3. Use a CASE Statement to Create a Table if It Does Not Exist in PostgreSQL

PostgreSQL is an object-relational database system which means that it can support much more complex data types than its contender, MySQL.

Aside from the difference mentioned, PostgreSQL tends to be very similar in structure when writing queries with MySQL or any other database system.

Because the backbone of each is RDBMS, all SQL queries are pretty much the same in both instances, with just extensions for each system that they may create individually.

Creating a table in PostgreSQL is very easy, and in this article, we will discuss all the ways we can write out queries that help us in table creation.

Use the CREATE TABLE Query to Create a Table if It Does Not Exist in PostgreSQL

One of the most straightforward queries to perform this operation is writing out a standard SQL CREATE TABLE, as shown below.

Create table if not exists table_one (
    u_name varchar(100) not null,
    PRIMARY KEY (u_name)
);

This query tends to check if the table, upon creation, does not exist and then creates it. If the table already exists, you will be hit up with a NOTICE like this.

Output:

NOTICE: relation "table_one" already exists, skipping

In addition to creating the table, you can check to see its working. You may use the INSERT command to enter the name and then call the SELECT operation to output the table.

INSERT into table_one values('John');

select * from table_one;

Output:

Use the CREATE or REPLACE Query to Create a Table if It Does Not Exist in PostgreSQL

We can also create a function to create a table with the CREATE or REPLACE method.

You may use the code given below to perform such an operation:

CREATE or replace FUNCTION create_user_specific_table()
    RETURNS void
    LANGUAGE plpgsql AS
$func$
BEGIN
    IF EXISTS (SELECT FROM pg_catalog.pg_tables
                WHERE  tablename  = 'table_one') THEN
        RAISE NOTICE 'Table table_one already exists.';
    ELSE
        CREATE TABLE table_one (u_name varchar(50));
    END IF;
END
$func$;

SELECT create_user_specific_table();

INSERT INTO table_one (u_name) values('Jonathon');

select * from table_one;

Output:

Now, let’s try to analyze how this works. You’ll see the CREATE or REPLACE written behind the function defined.

CREATE or REPLACE tends to replace the existing function definition given inside the system if it has already been integrated inside the database. Hence, you won’t run into an error while calling the function again and again.

It can be used effectively for testing, rather than deleting the function listed and then creating it again.

The $func$ is the start and end tag of the function. Inside the function, if the table is already present inside the listed tables, it won’t be created.

Instead, it will raise a notice that The table already exists. The RAISE NOTICE function is what implements this functionality.

Different implementations of this function can be used to CREATE a table in PostgreSQL. You can also modify this function as it fits better to your needs.

Use a CASE Statement to Create a Table if It Does Not Exist in PostgreSQL

CREATE OR REPLACE FUNCTION create_user_specific_table()
    RETURNS void
    LANGUAGE plpgsql AS
$func$
BEGIN
    IF EXISTS (SELECT FROM pg_catalog.pg_tables
                WHERE  tablename  = 'table_one') THEN
        RAISE NOTICE 'Table table_one already exists.';
    ELSE
        CREATE TABLE table_one (u_name varchar(50) not null
                                , PRIMARY KEY (u_name));
    END IF;
END
$func$;
SELECT CASE WHEN (SELECT true::BOOLEAN
    FROM   pg_catalog.pg_tables
    WHERE  tablename  = 'table_one'
    ) THEN (SELECT 'success'::void)
    ELSE (SELECT create_user_specific_table())
END;

INSERT INTO table_one (u_name) values('Jonathon');

select * from table_one;

The above code is no different than the method given above. It replaces the simple SELECT query with a CASE statement.

The SELECT CASE is the same as an if else statement. It returns a true when tablename is equal to table_one.

If the table already exists, it would return a Success; else, it would call the table creation function. It is pretty simple and can be used effectively to implement an IF EXISTS in a CASE notation.