3 Ways to Create a Table in PostgreSQL if It Does Not Exist

Introduction

PostgreSQL, often referred to as Postgres, is a powerful open-source object-relational database system. When working with databases, it’s crucial to ensure that you are making changes idempotently, particularly when you’re creating tables. One common task is to create a table only if it does not exist to prevent errors. This guide will walk you through multiple solutions to this problem in PostgreSQL.

Solution 1: CREATE TABLE IF NOT EXISTS

This is the most straightforward approach using PostgreSQL’s built-in SQL command, which checks for the existence of the table before attempting to create it.

Steps:

• Step 1: Open your PostgreSQL Command Line Interface (CLI) or use your favorite GUI client.
• Step 2: Ensure you have selected the correct database where you want the table to be created.
• Step 3: Write and execute the SQL command.

Example:

CREATE TABLE IF NOT EXISTS my_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Advantages: This solution is easy to use and requires minimal code.

Limitations: It doesn’t provide additional processing or error handling beyond table creation, and it’s PostgreSQL-specific, lacking portability.

Solution 2: Use a Custom Function

Create a PostgreSQL function that checks for a table’s existence and creates it if not found.
This allows for extended functionality and custom error handling.

Steps:

• Step 1: Define and create the function.
• Step 2: Call the function to create the table if needed.

Example:

CREATE OR REPLACE FUNCTION create_table_if_not_exists() RETURNS void AS $
BEGIN
    IF NOT EXISTS (
        SELECT FROM pg_catalog.pg_tables 
        WHERE schemaname = 'public' AND tablename  = 'my_table') THEN
        CREATE TABLE public.my_table (
           id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL
        );
    END IF;
END;
$ LANGUAGE plpgsql;

SELECT create_table_if_not_exists();

Advantages: Enhanced flexibility and control over the table-creation process.

Limitations: More complex than the basic command, and may require additional maintenance.

Solution 3: Use a DO Block

An anonymous code block (DO block) can be used for a one-time operation without the need to create a permanent function.

In this case, just use a DO block with an IF NOT EXISTS check within it to conditionally create the table.

Example:

DO $
BEGIN
    IF NOT EXISTS (
        SELECT FROM pg_catalog.pg_tables 
        WHERE schemaname = 'public' AND tablename  = 'my_table') THEN
        CREATE TABLE public.my_table (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL
        );
    END IF;
END
$;

Advantages: More straightforward than creating a function; no need for maintenance.

Limitations: Less reusable as it’s an anonymous block; also limited to offered control structures within the block.

Conclusion

Creating a table in PostgreSQL only if it does not exist is crucial for maintaining the integrity of a database schema without causing errors due to existing objects. The decision on which method to use depends on your needs for simplicity, reusability, and control. Using the CREATE TABLE IF NOT EXISTS command offers a quick and clean solution for most cases, while custom functions and DO blocks provide increased control for more complex scenarios. Regardless of the method chosen, it’s essential to understand the implications for maintainability and clarity of your database code.