database already exists

This error occurs when you try to create a database with a name that's already in use.

The database already exists error means the database name is already taken.

Understanding the Error

ERROR: database "myapp" already exists

A database with that name already exists on the server.

Common Causes

1. Running CREATE DATABASE Twice

SQL

CREATE DATABASE myapp;  -- Works
CREATE DATABASE myapp;  -- Error: already exists

2. Script Run Multiple Times

Migration or setup script executed more than once.

3. Name Conflict with Different Case

SQL

CREATE DATABASE myapp;    -- Creates 'myapp'
CREATE DATABASE MyApp;    -- Same as 'myapp' without quotes

How to Fix It

Solution 1: Check If Database Exists First

SQL

-- List all databases
SELECT datname FROM pg_database;

-- Or use psql
\l

Solution 2: Use IF NOT EXISTS

Note: PostgreSQL doesn't support CREATE DATABASE IF NOT EXISTS directly, but you can use:

SQL

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

Solution 3: Drop and Recreate

SQL

-- Warning: This deletes all data!
DROP DATABASE IF EXISTS myapp;
CREATE DATABASE myapp;

Solution 4: Use a Different Name

SQL

CREATE DATABASE myapp_v2;
-- Or with environment suffix
CREATE DATABASE myapp_development;
CREATE DATABASE myapp_test;

Solution 5: Shell Script Check

BASH

#!/bin/bash
DB_NAME="myapp"

if psql -lqt | cut -d \| -f 1 | grep -qw $DB_NAME; then
  echo "Database $DB_NAME already exists"
else
  createdb $DB_NAME
  echo "Database $DB_NAME created"
fi

Solution 6: Use Conditional in Migrations

JAVASCRIPT

// In Node.js migration
async function createDatabase(name) {
  const result = await client.query(
    "SELECT 1 FROM pg_database WHERE datname = $1",
    [name]
  );

  if (result.rows.length === 0) {
    await client.query(`CREATE DATABASE ${name}`);
  }
}

Best Practices

Use environment-specific names (myapp_dev, myapp_test, myapp_prod)
Check before creating in scripts
Use idempotent migrations that can run multiple times safely
Document database naming conventions
Avoid dropping production databases in scripts