How to fix Database already exists in PostgreSQL

Connect to PostgreSQL and query the system catalog to see all existing databases:

psql -U postgres -c "SELECT datname FROM pg_database WHERE datname = 'your_database';"

If you see output with your database name, it exists and you need to decide whether to drop it or use a different approach.

If you want to recreate the database from scratch and have no critical data, drop it first:

DROP DATABASE your_database;

Then create it again:

CREATE DATABASE your_database;

WARNING: This permanently deletes all data. Only use this if you have backups or the data is not important.

PostgreSQL does not support CREATE DATABASE IF NOT EXISTS, but you can use a subquery with the \gexec meta-command in psql:

SELECT 'CREATE DATABASE your_database' 
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'your_database')
\gexec

This will only execute the CREATE DATABASE statement if the database does not already exist. This approach works in psql scripts and shell scripts using psql.

For more complex scenarios or application code, use an anonymous PL/pgSQL block:

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

This ensures the database is only created if it does not exist. Note: dblink must be installed (CREATE EXTENSION IF NOT EXISTS dblink;).

For shell-based database initialization, check existence before calling createdb:

#!/bin/bash
DB_NAME="your_database"
DB_EXISTS=$(psql -U postgres -tAc "SELECT 1 FROM pg_database WHERE datname='${DB_NAME}'")
if [ "${DB_EXISTS}" != "1" ]; then
  createdb -U postgres ${DB_NAME}
  echo "Database ${DB_NAME} created."
else
  echo "Database ${DB_NAME} already exists. Skipping creation."
fi

This is a robust, idempotent approach for deployment automation.

If you are using an ORM, migration tool, or framework that generates database creation scripts, configure it to:

- Use conditional creation logic (the subquery workaround above)
- Skip database creation if the database already exists
- Use DROP IF EXISTS before CREATE DATABASE in destructive migration scenarios

Many tools like Prisma, SQLAlchemy, and TypeORM have options to control this behavior. Check your framework's documentation for idempotent database initialization.