This error occurs when you attempt to create a database with CREATE DATABASE but a database with that name already exists. PostgreSQL does not support the IF NOT EXISTS clause for databases, so you must either drop the existing database, use a workaround query, or choose a different database name.
PostgreSQL throws the "Database already exists" error when you try to create a database using the CREATE DATABASE command, but a database with the same name is already present in the PostgreSQL cluster. Unlike some other database systems, PostgreSQL does not provide a native IF NOT EXISTS clause for the CREATE DATABASE statement. This means every CREATE DATABASE command will fail if the target database name is already in use, regardless of whether you intended to skip creation or reuse an existing database. This error typically occurs during application setup, deployment, or restoration workflows where database creation scripts are run multiple times or against different environments.
Connect to PostgreSQL and query the system catalog to see all existing databases:
psql -U postgres -c "SELECT datname FROM pg_database WHERE datname = 'your_database';"If you see output with your database name, it exists and you need to decide whether to drop it or use a different approach.
If you want to recreate the database from scratch and have no critical data, drop it first:
DROP DATABASE your_database;Then create it again:
CREATE DATABASE your_database;WARNING: This permanently deletes all data. Only use this if you have backups or the data is not important.
PostgreSQL does not support CREATE DATABASE IF NOT EXISTS, but you can use a subquery with the \gexec meta-command in psql:
SELECT 'CREATE DATABASE your_database'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'your_database')
\gexecThis will only execute the CREATE DATABASE statement if the database does not already exist. This approach works in psql scripts and shell scripts using psql.
For more complex scenarios or application code, use an anonymous PL/pgSQL block:
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'your_database') THEN
PERFORM dblink_exec('dbname=postgres', 'CREATE DATABASE your_database');
END IF;
END $$;This ensures the database is only created if it does not exist. Note: dblink must be installed (CREATE EXTENSION IF NOT EXISTS dblink;).
For shell-based database initialization, check existence before calling createdb:
#!/bin/bash
DB_NAME="your_database"
DB_EXISTS=$(psql -U postgres -tAc "SELECT 1 FROM pg_database WHERE datname='${DB_NAME}'")
if [ "${DB_EXISTS}" != "1" ]; then
createdb -U postgres ${DB_NAME}
echo "Database ${DB_NAME} created."
else
echo "Database ${DB_NAME} already exists. Skipping creation."
fiThis is a robust, idempotent approach for deployment automation.
If you are using an ORM, migration tool, or framework that generates database creation scripts, configure it to:
- Use conditional creation logic (the subquery workaround above)
- Skip database creation if the database already exists
- Use DROP IF EXISTS before CREATE DATABASE in destructive migration scenarios
Many tools like Prisma, SQLAlchemy, and TypeORM have options to control this behavior. Check your framework's documentation for idempotent database initialization.
PostgreSQL intentionally lacks IF NOT EXISTS for databases to prevent accidental operations on wrong databases and to maintain clarity in schema management. This is a design decision to enforce explicit intent.
For containerized deployments, consider using Docker volumes with named volumes that persist across restarts, eliminating the need to recreate databases. For Kubernetes, use Helm or Operators that manage database lifecycle properly.
If you are using pg_restore from a backup, use the --clean flag to drop objects before recreating them: pg_restore --clean --create -U postgres backup.dump
Always ensure proper access control: CREATE DATABASE requires superuser or CREATEDB role privilege. Verify the user running the script has appropriate permissions.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL