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

Creating Tables Conditionally in PostgreSQL

In MySQL, the CREATE TABLE IF NOT EXISTS syntax allows you to create a table only if it doesn't already exist. This prevents accidental table re-creation when running a script multiple times.

PostgreSQL 9.1 and Later

PostgreSQL 9.1 introduced the same functionality as MySQL:

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

Pre-PostgreSQL 9.1

For older versions of PostgreSQL, you can use a workaround function:

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 the function to create the table:

SELECT create_mytable();

Notes:

• The pg_tables table is case-sensitive.
• The function only checks for actual tables, not related objects.
• Consider using the SECURITY DEFINER keyword if the executing role lacks the necessary privileges.

The above is the detailed content of How Can I 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!