PostgreSQL create table if not exists

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?

Solution 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.

Solution 2:

Try this:

CREATE TABLE IF NOT EXISTS app_user (
  username varchar(45) NOT NULL,
  password varchar(450) NOT NULL,
  enabled integer NOT NULL DEFAULT '1',
  PRIMARY KEY (username)
)

Solution 3:

I created a generic solution out of the existing answers which can be reused for any table:

CREATE OR REPLACE FUNCTION create_if_not_exists (table_name text, create_stmt text)
RETURNS text AS
$_$
BEGIN

IF EXISTS (
    SELECT *
    FROM   pg_catalog.pg_tables 
    WHERE    tablename  = table_name
    ) THEN
   RETURN 'TABLE ' || '''' || table_name || '''' || ' ALREADY EXISTS';
ELSE
   EXECUTE create_stmt;
   RETURN 'CREATED';
END IF;

END;
$_$ LANGUAGE plpgsql;

Usage:

select create_if_not_exists('my_table', 'CREATE TABLE my_table (id integer NOT NULL);');

It could be simplified further to take just one parameter if one would extract the table name out of the query parameter. Also I left out the schemas.

Solution 4:

This solution is somewhat similar to the answer by Erwin Brandstetter, but uses only the sql language.

Not all PostgreSQL installations has the plpqsql language by default, this means you may have to call CREATE LANGUAGE plpgsql before creating the function, and afterwards have to remove the language again, to leave the database in the same state as it was before (but only if the database did not have the plpgsql language to begin with). See how the complexity grows?

Adding the plpgsql may not be issue if you are running your script locally, however, if the script is used to set up schema at a customer it may not be desirable to leave changes like this in the customers database.

This solution is inspired by a post by Andreas Scherbaum.

-- Function which creates table
CREATE OR REPLACE FUNCTION create_table () RETURNS TEXT AS $$
    CREATE TABLE table_name (
       i int
    );
    SELECT 'extended_recycle_bin created'::TEXT;
    $$
LANGUAGE 'sql';

-- Test if table exists, and if not create it
SELECT CASE WHEN (SELECT true::BOOLEAN
    FROM   pg_catalog.pg_tables 
    WHERE  schemaname = 'public'
    AND    tablename  = 'table_name'
  ) THEN (SELECT 'success'::TEXT)
  ELSE (SELECT create_table())
END;

-- Drop function
DROP FUNCTION create_table();