How to fix Duplicate database in PostgreSQL

Connect to PostgreSQL and view all databases:

\l

Or use SQL directly:

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.

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

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.

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

CREATE DATABASE my_database_v2;

Or include a timestamp or environment identifier in the name:

CREATE DATABASE my_database_prod_2025;

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

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.

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

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.