// PostgreSQL error reference

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
Category
Syntax Error or Access Rule Violation
Error message
database "shop" already exists

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 DATABASE every time it starts.
  • Tests that create a fixed-name database without dropping it afterwards.
  • Restoring with pg_restore --create onto a server that already has a database of that name.

How to fix it

  1. Check whether it exists first.

    SELECT 1 FROM pg_database WHERE datname = 'shop';
  2. 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
  3. 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

More syntax error or access rule violation errors

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.

// fewer errors, faster fixes

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.