PostgreSQL create table if not exists

Question

In a MySQL script you can write:

CREATE TABLE IF NOT EXISTS foo ...;

... other stuff ...

and then you can run the script many times without re-creating the table.

How do you do this in PostgreSQL?

Answer 1

I don't think you can (you can do it with other objects, though - views, I think). You have to drop the table first (if it exists) and then create.

According to this link:

http://blog.thinkopen.co.uk/2007/01/drop-table-if-exists-in-postgresql.html

you can use DROP TABLE IF EXISTS from postgresql 8.2 onwards.

Or this, for older versions of postgresql:

if exists (select 1 from pg_tables where tablename = "thetable")
drop table thetable

Answer 2

There is no CREATE TABLE IF NOT EXISTS... but you can write a simple procedure for that, something like:

CREATE OR REPLACE FUNCTION execute(TEXT) RETURNS VOID AS $$
BEGIN
  EXECUTE $1;
END; $$ LANGUAGE plpgsql;


SELECT 
  execute($$
      CREATE TABLE sch.foo 
      (
        i integer
      )
  $$) 
WHERE 
  NOT exists 
  (
    SELECT * 
    FROM information_schema.tables 
    WHERE table_name = 'foo'
      AND table_schema = 'sch'
  );

That's a little bit weird, but can simply be