Postgresql: How to create a table only if it does not already exist?

Compatibility check with your PostgreSQL Version

Make sure your PostgreSQL supports the CREATE TABLE IF NOT EXISTS command. This feature is available from PostgreSQL 9.1 and above. If your PostgreSQL version is prior to 9.1, you need to resort to other methods such as using information_schema or PL/pgSQL functions.

You can quickly check your PostgreSQL version by executing:

SELECT version();


If the result shows version 9.1 or above, CREATE TABLE IF NOT EXISTS is at your service!

Checking table existence using information_schema or pg_class

If you're stuck with an older version of PostgreSQL or need a second layer of table existence verification, you can use information_schema or pg_class.

Using information_schema:

SELECT to_regclass('schema.table_name') IS NOT NULL AS exists;


Using pg_class:

SELECT COUNT(*) FROM pg_class WHERE relname = 'table_name';


If the count is greater than 0, the table exists, pat yourself on the back—you have not created duplicates! 👏

Remember:

• Queries on system catalog (like pg_class) could be slower on large databases, making snails look like Usain Bolt.
• This script has schema awareness, meaning if a table exists in multiple schemas, you should specify the schema, or you might get misleading results.

Cases when IF NOT EXISTS might not be your best companion

IF NOT EXISTS might not be a one-size-fits-all solution. Here are some situations when you'd better not to use IF NOT EXISTS:

• When you need transactional DDL operations. IF NOT EXISTS doesn't play well with them.
• When you use a migration framework that tracks state. IF NOT EXISTS might confuse it.
• In high concurrency environments where two threads may create the same table simultaneously—IF NOT EXISTS won't be fast enough.

Wrapping logic in PL/pgSQL for complex requirements

If there’s a need to throw more complex logic or exception handling into the play, you can wrap your logic in a PL/pgSQL function:

CREATE OR REPLACE FUNCTION create_table_if_not_exists() RETURNS void LANGUAGE plpgsql AS
$$
BEGIN
   IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'public' AND tablename = 'table_name') THEN
      CREATE TABLE public.table_name (...);
   END IF;
END
$$;


You can invoke this function and then drop it right after execution, to keep your database clean as a new pin:

SELECT create_table_if_not_exists();
DROP FUNCTION create_table_if_not_exists();


Balancing between efficiency, reliability, and not falling asleep

While adding checks seems to provide more reliability, note that they often require more database interaction and could potentially be a performance hit. It's like adding more layers to your lasagna—it gets taller but takes more time to cook. Always balance efficiency against the need for error prevention.

Practical use cases

• Scripted deployments: Automate the creation of tables, only when required, to avoid manual errors. It's like brewing coffee only if there isn't a hot pot waiting for you already.
• Test case setups: Prevent conflicts in a shared development database.
• Database migration scripts: Make schema upgrades smoother.

Comprehensive solutions: More than one way to cook an egg

While CREATE TABLE IF NOT EXISTS is very handy, take note that there are more approaches you can use based on your situation:

1. Database Migration Tools: Tools like Flyway or Liquibase handle schema changes and can conditionally apply changes including table creation.
2. Custom Scripting: Writing scripts that check for table existence before performing creations. It's like asking, "Is there milk?" before pouring yourself a bowl of cereal.
3. Extensions: Some PostgreSQL extensions offer additional functionality for more complex scenarios.

Using the ideal strategy will not only solve the immediate problem but also set a proper foundation for future database operations.