PostgreSQL 42P04: database already exists
CREATE DATABASE used a name an existing database already has on this server. Database names are unique per cluster.
- SQLSTATE
42P04- Name
duplicate_database
What it means
All databases in a PostgreSQL cluster (one server instance) share one namespace, so CREATE DATABASE fails with 42P04 if the name is taken. Unlike tables, CREATE DATABASE has no IF NOT EXISTS clause and can't run inside a transaction block or a DO block, so scripts have to check before calling it.
Common causes
- A setup script or container entrypoint that runs
CREATE DATABASEevery time it starts. - Tests that create a fixed-name database without dropping it afterwards.
- Restoring with
pg_restore --createonto a server that already has a database of that name.
How to fix it
-
Check whether it exists first.
SELECT 1 FROM pg_database WHERE datname = 'shop';
-
In psql scripts, create it only when missing with
\gexec.SELECT 'CREATE DATABASE shop' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'shop')\gexec
-
If the old database should be replaced, drop it first (it must have no connections).
DROP DATABASE IF EXISTS shop WITH (FORCE); CREATE DATABASE shop;
Related errors
3D000database does not exist55006object in use42P06schema already exists42P07relation already exists25001cannot run inside a transaction block
More syntax error or access rule violation errors
42601syntax error at or near42501permission denied42846cannot coerce42803column must appear in the GROUP BY clause42830invalid foreign key42804datatype mismatch42P18could not determine data type of parameter42703column does not exist
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.