Guide to Conditional Database Creation in PostgreSQL

PostgreSQL: Create Database If Not Exists

PostgreSQL does not have a direct CREATE DATABASE IF NOT EXISTS clause, as seen in some other relational databases like MySQL. However, this functionality can be emulated by querying the system catalog to check for the existence of the database and creating it conditionally.

This guide provides detailed steps to create a database in PostgreSQL only if it does not already exist. We include syntax, examples, and an explanation for better understanding.

Syntax:

Since PostgreSQL lacks the IF NOT EXISTS clause for database creation, you can use a combination of a conditional query and dynamic SQL within a block. Here's the syntax:

 
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_database WHERE datname = 'database_name'
    ) THEN
        PERFORM dblink_exec('dbname=postgres', 'CREATE DATABASE database_name');
    END IF;
END $$;

Example 1: Using pg_database to Check for Existence

Code:

-- Check if the database 'testdb' exists and create it if it does not
DO $$
BEGIN
    IF NOT EXISTS (
        -- Query the system catalog to check for the database
        SELECT 1 FROM pg_database WHERE datname = 'testdb'
    ) THEN
        -- Execute the CREATE DATABASE statement
        PERFORM dblink_exec('dbname=postgres', 'CREATE DATABASE testdb');
    END IF;
END $$;

Explanation:

1. System Catalog Check:

• The pg_database catalog contains metadata about existing databases.
• The query checks if testdb exists.

2. Dynamic SQL Execution:

• dblink_exec is used to execute the CREATE DATABASE command dynamically.

3. Conditional Logic:

• The database is created only if it does not exist.

Example 2: Emulating Behavior Without Extensions

If dblink is unavailable, you can use a script outside PostgreSQL to achieve similar functionality:

Code:

#!/bin/bash
# Check if the database exists
DB_NAME="testdb"
DB_EXISTS=$(psql -U postgres -tAc "SELECT 1 FROM pg_database WHERE datname='${DB_NAME}'")

if [ "${DB_EXISTS}" != "1" ]; then
  # Create the database
  createdb -U postgres ${DB_NAME}
  echo "Database ${DB_NAME} created."
else
  echo "Database ${DB_NAME} already exists."
fi

Explanation

1. Query Execution:

• The psql command-line tool queries the system catalog.

2. Shell Script Logic:

• A conditional check determines if the database exists and creates it if needed.

Common Use Cases

1. Automated Deployments: Ensure that required databases are created automatically in deployment scripts.

2. Testing Environments: Dynamically set up databases for test cases without manual intervention.

Additional Notes

• Permissions: Ensure the user executing the script has the necessary privileges to create databases.
• Extensions: If dblink is not installed, you can replace it with external scripts or tools.
• Error Handling: Consider adding error-handling mechanisms to log or manage issues during database creation.

All PostgreSQL Questions, Answers, and Code Snippets Collection.