How to fix Duplicate database in PostgreSQL

PostgreSQLBEGINNERMEDIUMUpdated Dec 27, 2025· 184 views

This error occurs when attempting to create a database with a name that already exists in your PostgreSQL cluster. Use IF NOT EXISTS or choose a different database name to resolve it.

Quick fix

Fixes 42P04

\l
  1. List all existing databases to verify the conflict
  2. Use CREATE DATABASE IF NOT EXISTS to skip existing databases
  3. Choose a different database name if needed
  4. Drop the existing database if you want to recreate it
postgresql — error
\l
Duplicate database

What this error means

PostgreSQL raises a "duplicate_database" error (code 42P04) when you try to create a new database using CREATE DATABASE with a name that is already in use in the same cluster. This safety mechanism prevents accidental data loss and confusion from multiple databases sharing the same name. The error is intentional and protects against overwriting or conflicting with existing databases.

Typical symptoms

  • CREATE DATABASE command fails with error code 42P04
  • Application deployment fails during database initialization
  • Database migration tools report database already exists
  • Automated scripts fail when trying to create fresh databases

Common causes

  1. Attempting to create a database with a name that already exists
  2. Running database creation scripts multiple times without checking for existing databases
  3. Restoring from backups without dropping old databases first
  4. Multiple deployment pipelines trying to create the same database simultaneously

How to fix "Duplicate database"

1List all existing databases to verify the conflict

Connect to PostgreSQL and view all databases:

sql
\l

Or use SQL directly:

sql
SELECT datname FROM pg_database WHERE datistemplate = false;

This will show you all non-template databases in your cluster and help you identify if the database you're trying to create already exists.

2Use CREATE DATABASE IF NOT EXISTS to skip existing databases

Modify your CREATE DATABASE statement to use the IF NOT EXISTS clause:

sql
CREATE DATABASE my_database IF NOT EXISTS;

This tells PostgreSQL to create the database only if it doesn't already exist, preventing the error without requiring manual intervention.

3Choose a different database name if needed

If you need a new separate database and the name conflicts, select a different name:

sql
CREATE DATABASE my_database_v2;

Or include a timestamp or environment identifier in the name:

sql
CREATE DATABASE my_database_prod_2025;
4Drop the existing database if you want to recreate it

If the existing database is no longer needed, you can drop it before creating a new one:

sql
DROP DATABASE my_database;
CREATE DATABASE my_database;

WARNING: This permanently deletes all data in that database. Make sure you have backups if the data is important.

5Terminate active connections before dropping

If you get an error that the database is in use, you must close all active connections first:

sql
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'my_database' AND pid != pg_backend_pid();

DROP DATABASE my_database;

This terminates all sessions connected to the database, allowing the DROP to proceed.

Advanced notes

When copying databases, use the CREATE DATABASE with TEMPLATE syntax instead of recreating from scratch:

sql
CREATE DATABASE my_database_copy WITH TEMPLATE original_database;

This is faster and more reliable than rebuilding. If the database copy destination already exists, use IF NOT EXISTS first. In automated deployment scripts (Docker, CI/CD), always include IF NOT EXISTS to make scripts idempotent—they can run multiple times safely. Some tools like Django and Doctrine ORM have specific handling for this error code (42P04) and will retry or skip creation appropriately when properly configured.

Frequently asked questions

What does "42P04" mean?
PostgreSQL raises a "duplicate_database" error (code 42P04) when you try to create a new database using CREATE DATABASE with a name that is already in use in the same cluster. This safety mechanism prevents accidental data loss and confusion from multiple databases sharing the same name. The error is intentional and protects against overwriting or conflicting with existing databases.
How do I fix "42P04"?
This error occurs when attempting to create a database with a name that already exists in your PostgreSQL cluster. Use IF NOT EXISTS or choose a different database name to resolve it.
What causes "42P04"?
Attempting to create a database with a name that already exists Running database creation scripts multiple times without checking for existing databases Restoring from backups without dropping old databases first
Is the "42P04" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading