How to Create Tables in PostgreSQL without Duplicates?

Creating Tables in PostgreSQL without Duplication

In MySQL, the "CREATE TABLE IF NOT EXISTS" syntax allows you to create a table without generating an error if it already exists. This ensures that the script can be run multiple times without creating duplicate tables. In PostgreSQL, this feature was introduced in version 9.1 onwards.

PostgreSQL 9.1 and Later

For PostgreSQL 9.1 and above, you can use the following syntax to create a table:

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

PostgreSQL Versions Prior to 9.1

For earlier versions of PostgreSQL, there is a workaround using a 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$;

You can then call the function as follows:

SELECT create_mytable();

Notes:

• Ensure your syntax matches the case sensitivity of the table's schema and name.
• Remember that pg_tables only contains actual tables, so it's possible for the identifier to be occupied by other objects.
• If the role executing the function lacks the required table creation privileges, consider granting those privileges using the SECURITY DEFINER clause.

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