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

Question

In Postgresql, how can I do a condition to create a table only if it does not already exist?

Code example appreciated.

Answer 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

Answer 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();