How Can I Safely Create PostgreSQL Tables Without Duplicate Table Errors?

PostgreSQL: Handling Table Creation with "CREATE TABLE IF NOT EXISTS"

In PostgreSQL, the use of "CREATE TABLE IF NOT EXISTS" allows for the creation of tables without the risk of duplicate table creation. This feature was introduced in PostgreSQL 9.1 and makes it easy to create tables in a manner that ensures they exist without the need to manually check for their presence.

Syntax for PostgreSQL 9.1 and Later

To create a table using "CREATE TABLE IF NOT EXISTS" in PostgreSQL 9.1 and later versions, simply specify the command as follows:

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

Note: Replace "myschema" and "mytable" with your desired schema and table names.

Workaround for Older Versions

For PostgreSQL versions prior to 9.1, a workaround function can be used to achieve similar functionality. Create the following 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 as needed:

SELECT create_mytable();

Notes:

• The columns "schemaname" and "tablename" in "pg_tables" are case-sensitive.
• "pg_tables" only contains actual tables, not related objects.
• Consider using "SECURITY DEFINER" if the role executing the function lacks create table privileges.

The above is the detailed content of How Can I Safely Create PostgreSQL Tables Without Duplicate Table Errors?. For more information, please follow other related articles on the PHP Chinese website!