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

This feature has been implemented in Postgres 9.1:

CREATE TABLE IF NOT EXISTS myschema.mytable (i integer);

---

For older versions, here is a function to work around it:

CREATE OR REPLACE FUNCTION create_mytable()
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF EXISTS (SELECT FROM pg_catalog.pg_tables 
              WHERE  schemaname = 'myschema'
              AND    tablename  = 'mytable') THEN
      RAISE NOTICE 'Table myschema.mytable already exists.';
   ELSE
      CREATE TABLE myschema.mytable (i integer);
   END IF;
END
$func$;

Call:

SELECT create_mytable();        -- call as many times as you want. 

Notes:

• The columns schemaname and tablename in pg_tables are case-sensitive. If you double-quote identifiers in the CREATE TABLE statement, you need to use the exact same spelling. If you don't, you need to use lower-case strings. See:

• Are PostgreSQL column names case-sensitive?

• pg_tables only contains actual tables. The identifier may still be occupied by related objects. See:

• How to check if a table exists in a given schema

• If the role executing this function does not have the necessary privileges to create the table you might want to use SECURITY DEFINER for the function and make it owned by another role with the necessary privileges. This version is safe enough.