How to Create Tables in PostgreSQL Using 'IF NOT EXISTS'?

How to Create Tables with PostgreSQL's "IF NOT EXISTS" Clause

MySQL allows users to create tables with the "IF NOT EXISTS" clause, ensuring that the table is only created if it doesn't already exist. This prevents errors if the script is run multiple times.

In PostgreSQL, this feature was introduced in version 9.1, allowing the use of:

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

For earlier versions of PostgreSQL, the following function can be used as a workaround:

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 as often as needed using:

SELECT create_mytable();

Notes:

• The schemaname and tablename columns in pg_tables are case-sensitive.
• pg_tables only contains actual tables, so the identifier may still be occupied by related objects.
• If the user executing the function does not have the necessary privileges to create the table, the SECURITY DEFINER attribute can be used for the function.

The above is the detailed content of How to Create Tables in PostgreSQL Using 'IF NOT EXISTS'?. For more information, please follow other related articles on the PHP Chinese website!