Postgresql: how to create table only if it does not already exist?

Solution 1:

I'm not sure when it was added, but for the sake of completeness I'd like to point out that in version 9.1 (maybe before) IF NOT EXISTS can be used. IF NOT EXISTS will only create the table if it doesn't exist already.

Example:

CREATE TABLE IF NOT EXISTS users.vip
(
  id integer
)

This will create a table named vip in the schema users if the table doesn't exist.

Source

Solution 2:

create or replace function update_the_db() returns void as
$$
begin

    if not exists(select * from information_schema.tables 
        where 
            table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA
            and table_name = 'your_table_name_here') then

        create table your_table_name_here
        (
            the_id int not null,
            name text
        );

    end if;

end;
$$
language 'plpgsql';

select update_the_db();
drop function update_the_db();

Solution 3:

Just create the table and don't worry about whether it exists. If it doesn't exist it will be created; if it does exist the table won't be modified. You can always check the return value of your SQL query to see whether the table existed or not when you executed the create statement.

Solution 4:

I think to check the pg_class table perhaps help you, something like that:

SELECT COUNT (relname) as a FROM pg_class WHERE relname = 'mytable'

if a = 0 then (CREATE IT)

Regards.