PostgreSQL create table if not exists

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.

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))

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$_$BEGINIF 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.