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

Question

I am totally new to postgresql. How can I do a condition to create a table only if it does not already exists? Code example appreciated.

Answer 1

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.

Answer 2

DROP TABLE blah IF EXISTS;
CREATE TABLE blah ...

Of course, this is only if you want a fresh table even if it means losing any existing data. Depends what you're trying to do in a larger sense.

Answer 3

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

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

if a = 0 then (CREATE IT)

Regards.

Answer 4

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

    if not exists(select * from information_schema.tables where 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();

Answer 5

The correct syntax at least in version 8.4 beta 2 is

DROP TABLE IF EXISTS TABLENAME;

Answer 6

create or replace function build_foo_table() returns void as $$
 create table foo_table (id int);
$$ language sql;

create or replace function foo_table_exists() returns int as $$
 select count(table_name)::int from information_schema.tables where table_name = 'foo_table';
$$ language sql;

select case when foo_table_exists()=0 then build_foo_table()end;

drop function build_foo_table();
drop function foo_table_exists();

Answer 7

http://www.postgresql.org/docs/8.2/static/sql-droptable.html

DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]