PostgreSQL CREATE DATABASE IF NOT EXISTS: The Workaround Postgres Does Not Have

Q: Can I create a database from a template?

Yes, `CREATE DATABASE mydb TEMPLATE template0`. The right answer is to use template0 for clean databases without PostgreSQL's standard objects.

PostgreSQL does not support CREATE DATABASE IF NOT EXISTS (unlike MySQL). The right workaround is to query pg_database first, then create the database if it does not exist. The wrong answer is to assume PostgreSQL has this and get a syntax error.

The standard SQL workaround
The DO block workaround
The shell script workaround
The right CREATE DATABASE syntax
Why PostgreSQL does not have IF NOT EXISTS
FAQ

The standard SQL workaround

The right way in plain SQL:

SELECT 'CREATE DATABASE mydb'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb')\gexec

This uses psql’s \gexec to run the SELECT result as a command. The right answer for one-off database creation is this.

The DO block workaround

The right way in a DO block:

DO $$
BEGIN
  IF NOT EXISTS (SELECT FROM pg_database WHERE datname = 'mydb') THEN
    CREATE DATABASE mydb;
  END IF;
END
$$;

The right answer for scripts is the DO block. The wrong answer is to wrap CREATE DATABASE in a transaction — CREATE DATABASE cannot run inside a transaction.

The shell script workaround

The right way in a shell script:

psql -U postgres -tc "SELECT 1 FROM pg_database WHERE datname = 'mydb'" | grep -q 1 || psql -U postgres -c "CREATE DATABASE mydb"

The right answer for shell scripts is the psql -tc check + \gexec or the grep -q 1 || create pattern.

The right CREATE DATABASE syntax

The right syntax:

CREATE DATABASE mydb WITH OWNER = alice ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8';

The right answer is to specify the owner and encoding. The wrong answer is to use the defaults and discover later that the encoding is wrong.

Why PostgreSQL does not have IF NOT EXISTS

The right answer is that CREATE DATABASE cannot run inside a transaction, and IF NOT EXISTS is a transaction-only feature in PostgreSQL. The right workaround is the patterns above.

FAQ

Why does not PostgreSQL have IF NOT EXISTS for CREATE DATABASE?

Because CREATE DATABASE cannot run inside a transaction, and IF NOT EXISTS is implemented as a transaction feature in PostgreSQL.

Can I use CREATE DATABASE IF NOT EXISTS in a function?

No. CREATE DATABASE cannot run inside a function or transaction.

What is the right way to create a database in a script?

The right way is to query pg_database first, then CREATE if not exists. See the workarounds above.

Can I use CREATE SCHEMA IF NOT EXISTS?

Yes, CREATE SCHEMA supports IF NOT EXISTS. The right answer is to use schemas within a single database to organize objects.

What is the right encoding for a new database?

UTF8. The right answer is to specify it explicitly to avoid surprises.

Can I create a database from a template?

Yes, CREATE DATABASE mydb TEMPLATE template0. The right answer is to use template0 for clean databases without PostgreSQL’s standard objects.

If you are sizing the infrastructure for the kind of project this post covers, the RunxBuild hosting calculator is the right place to model the line items. The compute, the memory, the storage, the bandwidth, the database - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers. The RunxBuild dashboard is where the team sees the actual usage in one place.

Useful related references: