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 AS
$func$
BEGIN
   IF EXISTS (SELECT 1 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$ LANGUAGE plpgsql;

Call:

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

If the user does not have the necessary privileges to create the table you might want to use SECURITY DEFINER. 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 (user_id)  
)

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. Feel free to extend my solution if you know how to do that - I'm not that deep into plpgsql yet (this is the first time I'm dealing with it).

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

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