Coder Perfect

PostgreSQL create table if not exists

Problem

You can write the following in a MySQL script:

CREATE TABLE IF NOT EXISTS foo ...;

… and a few other things…

After that, you can execute the script as many times as you like without having to recreate the table.

In PostgreSQL, how do you do this?

Asked by peter2108

Solution #1

This feature is now available in Postgres 9.1:

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

Here’s a function to work around it in older versions:

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. 

In pg tables, the columns schemaname and tablename are case-sensitive. You must use the exact same spelling when double-quoting identifiers in the CREATE TABLE statement. If you don’t, lower-case strings must be used. See:

Only actual tables are stored in pg tables. Related items may still occupy the identifier. See:

If the role running this function does not have the requisite privileges to construct the table, you can use SECURITY DEFINER to make the function owned by a role that does. This version is very safe.

Answered by Erwin Brandstetter

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

Answered by Achilles Ram Nakirekanti

Solution #3

Out of the current responses, I constructed a general solution that can be used 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);');

If the table name could be extracted from the query argument, it might be reduced to just one parameter. I also skipped through the schema.

Answered by Wolkenarchitekt

Solution #4

This approach is similar to Erwin Brandstetter’s answer, but it just employs the sql language.

Because the plpqsql language is not installed by default in all PostgreSQL installations, you may need to use CREATE LANGUAGE plpgsql before creating the function, and then remove the language to restore the database to its previous state (but only if the database did not have the plpgsql language to begin with). Observe how the level of difficulty rises.

If you are running your script locally, adding the plpgsql may not be an issue; but, if the script is used to build up schema at a customer, it may not be acceptable to leave changes like this in the customer’s database.

Andreas Scherbaum’s post provided the inspiration for our idea.

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

Answered by zpon

Solution #5

Although there is no CREATE TABLE IF NOT EXISTS… command, you can build a simple method to do it, such as:

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

Answered by Szymon LipiƄski

Post is based on https://stackoverflow.com/questions/1766046/postgresql-create-table-if-not-exists