Postgresql create table if not exists

Case sensitivity in PostgreSQL

PostgreSQL treats identifiers as case-sensitive. Here's an enlightening nugget - when committing to CREATE TABLE IF NOT EXISTS, lower-case table names are your friends:

-- Sensible usage with lower-case table names, they won't stab you in the back
CREATE TABLE IF NOT EXISTS my_table (
    id SERIAL PRIMARY KEY,
    info TEXT
);


Remember, this practice dances well with the PostgreSQL naming conventions and struts elegantly with how identifiers are stored in the pg_catalog.pg_tables catalog.

Defining SECURITY DEFINER

Feel like containing the wildfire? When limiting access to the table creation process, consider a function with SECURITY DEFINER:

CREATE OR REPLACE FUNCTION create_my_table()
RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
    -- Ask pg_tables, "Hey, do we have 'my_table' in the 'public' schema?"
    IF NOT EXISTS (SELECT FROM pg_catalog.pg_tables 
                   WHERE schemaname = 'public' AND tablename = 'my_table') THEN
        EXECUTE 'CREATE TABLE public.my_table (
            id SERIAL PRIMARY KEY,
            info TEXT
        )';
    END IF;
END;
$$;

-- Call the function and watch it strut its stuff
SELECT create_my_table();


This ensures the function runs with the privileges of the user who created it, rather than the user doing the invoking. Trust me, it's a good trick to have up your sleeve.

Checking role privileges

Before building your Rome, let's ensure the role has the privileges to create a table:

-- Stand-up Comedy with PostgreSQL: Does user have "creation" rights or only "viewing" rights?
SELECT has_schema_privilege('current_user', 'public', 'CREATE');


A truthful return puts you in the clear, saving you from any unexpected authorization bloopers.

Reusable logic with CREATE OR REPLACE FUNCTION

Encapsulate your logic for creating tables in a PL/pgSQL function because, let's be honest, who wants to repeat all that work:

CREATE OR REPLACE FUNCTION create_unique_table(tablename TEXT)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    -- Check if the production line already made this table
    IF NOT EXISTS (SELECT FROM pg_tables WHERE tablename = lower($1)) THEN
        EXECUTE format('CREATE TABLE %I (id SERIAL PRIMARY KEY)', $1);
    END IF;
END;
$$;

-- Let's test it out!
SELECT create_unique_table('unique_table');


This neat little function will confirm the table's existence before even attempting to create it, and our friend %I ensures we're on the safe side of SQL injection risks.

Adding defaults and constraints

A successful table structure also includes defining primary keys and setting default values:

-- Sophisticated Table Representation
CREATE TABLE IF NOT EXISTS my_table (
    id SERIAL PRIMARY KEY,
    info TEXT DEFAULT 'default info'
);


Better data integrity and more meaningful default values are the icing on the coding cake.

Clearing the stage after the act

Once the play (tables) have been staged (created), it's time to clean up:

-- Clear the props off the stage
DROP FUNCTION IF EXISTS create_my_table();


This cleans up your database environment, just like a tidy code, is a joy forever.

Double-checking with information_schema

The information_schema.tables catalog provides another layer of assurance for table existence:

-- Just to be doubly sure
SELECT 
    * 
FROM 
    information_schema.tables 
WHERE 
    table_schema = 'public' 
    AND table_name = 'my_table';


If this returns no rows, your table doesn't exist. If it returns one or more rows, bingo! It exists.

Exercising caution with database changes

Before re-decorating a customer's database, always exercise extreme caution. Always, always ensure necessary backups and permissions are in order to avoid accidentally causing a database apocalypse.