How to Create a Table in PostgreSQL Only If It Doesn't Already Exist?

Creating Tables with "CREATE TABLE IF NOT EXISTS" in PostgreSQL

The ability to create a table only if it does not already exist is a useful feature for ensuring that tables are only created once, even if a script is run multiple times.

In MySQL, this feature can be implemented using the statement:

CREATE TABLE IF NOT EXISTS foo ...;

PostgreSQL

In PostgreSQL versions 9.1 and later, the "CREATE TABLE IF NOT EXISTS" syntax is supported:

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

Workaround for Older Versions

For PostgreSQL versions before 9.1, a function can be used to achieve the same functionality:

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$;

This function can be called multiple times to create the table only if it does not exist.

Notes:

• The function create_mytable() assumes that the table will be created in the myschema schema.
• If the user executing this function does not have the necessary privileges to create the table, the SECURITY DEFINER attribute may need to be used to ensure successful execution.

The above is the detailed content of How to Create a Table in PostgreSQL Only If It Doesn't Already Exist?. For more information, please follow other related articles on the PHP Chinese website!