PostgreSQL CREATE DATABASE

Basic syntax

CREATE DATABASE name
    [ WITH ]
    [ OWNER [=] user_name ]
    [ ENCODING [=] encoding ]
    [ LC_COLLATE [=] lc_collate ]
    [ LC_CTYPE [=] lc_ctype ]
    [ TEMPLATE [=] template ]
    [ CONNECTION LIMIT [=] connlimit ];

All options are optional. The minimum is a name. Options can appear in any order and the WITH keyword is also optional.

Creating a database

Connect to your PostgreSQL instance and run:

CREATE DATABASE myapp;

PostgreSQL creates the database using template1 as the default template, inherits the server's default encoding and locale, and assigns ownership to the current user.

If the database already exists, this fails with database "myapp" already exists. Unlike MySQL, PostgreSQL has no IF NOT EXISTS clause for CREATE DATABASE. See CREATE DATABASE IF NOT EXISTS below for what to do instead.

createdb command-line tool

PostgreSQL ships with createdb, a shell wrapper around CREATE DATABASE. You can create a database without opening psql:

createdb myapp

With options:

createdb --owner=alice --encoding=UTF8 myapp

createdb accepts the same connection flags as psql (-h, -p, -U). It is available on any machine with the PostgreSQL client tools installed.

Setting the owner

By default, the new database is owned by the user who runs the statement. To assign a different owner:

CREATE DATABASE myapp OWNER alice;

Only a superuser can create a database owned by another role. The owner can drop the database and change its options later. They do not need to be a superuser themselves.

Setting encoding and locale

For new databases, always specify UTF8 explicitly. This avoids surprises when your server's default encoding is something else:

CREATE DATABASE myapp
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8'
    TEMPLATE template0;

LC_COLLATE controls sort order. LC_CTYPE controls character classification (uppercase, lowercase, digit). Both default to the locale of template1.

When you change encoding or locale from template1's values, you must use TEMPLATE template0 (see the next section). PostgreSQL will reject the statement otherwise.

Template databases

Every new database is a copy of a template. PostgreSQL ships with two built-in templates.

To create a clean database with a specific encoding:

CREATE DATABASE myapp
    ENCODING 'UTF8'
    LC_COLLATE 'C'
    LC_CTYPE 'C'
    TEMPLATE template0;

The C locale uses byte-order comparison, which is faster and more portable than a language-specific locale. It is a good default when you do not need language-aware sorting.

You can also use any regular database as a template. Any database can serve as a template if no other sessions are connected to it, which is useful for creating multiple identical databases.

CREATE DATABASE myapp_staging TEMPLATE myapp_production;

Connection limits

To cap how many concurrent connections a database accepts:

CREATE DATABASE myapp CONNECTION LIMIT 100;

The default is -1, which means no limit. Setting a limit protects other databases on the same server from a single database consuming all available connections.

To remove a limit after creation:

ALTER DATABASE myapp CONNECTION LIMIT -1;

CREATE DATABASE IF NOT EXISTS

PostgreSQL does not support it. This is a MySQL command, and there is no equivalent clause:

CREATE DATABASE IF NOT EXISTS myapp;

ERROR:  syntax error at or near "NOT"
LINE 1: CREATE DATABASE IF NOT EXISTS myapp;
                           ^

The caret points at NOT, not at IF, which is a small mystery worth explaining: if is an unreserved keyword, so PostgreSQL happily reads it as the database name you wanted, then hits the reserved word NOT and gives up.

This is deliberate rather than an oversight. The objection, from Tom Lane on the pgsql-hackers list, is that the semantics are vague: it guarantees a database by that name exists, but tells you nothing about its properties or contents.

DROP DATABASE IF EXISTS does exist. Only the CREATE side is missing.

Why you cannot wrap it in a DO block

The obvious workaround does not work either, because CREATE DATABASE cannot run inside a transaction:

BEGIN;
CREATE DATABASE myapp;

ERROR:  CREATE DATABASE cannot run inside a transaction block

And from a function or DO block, which is implicitly a transaction:

ERROR:  CREATE DATABASE cannot be executed from a function

There is a related trap here: psql -c wraps multiple statements in a single transaction, so this fails too:

psql -c "SELECT 1; CREATE DATABASE myapp;"

ERROR:  CREATE DATABASE cannot run inside a transaction block

Working alternatives

\gexec in psql. Generate the statement, then execute it. Idempotent, and it runs outside a transaction:

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

First run prints CREATE DATABASE. Second run does nothing at all.

One gotcha that costs people real time: \gexec does not work with psql -c:

ERROR:  syntax error at or near "\"

\gexec is a psql meta-command, and -c sends its argument to the server as SQL. Pipe it in on stdin or use -f with a file.

A shell guard. For scripts and CI:

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

Note the ||. A version of this circulates widely with a single pipe (| grep -q 1 |) instead, which never gates anything, because grep -q writes no output and psql -c ignores stdin. It looks like it works right up until the database already exists.

Just ignore the error. If a duplicate is harmless, createdb myapp || true is honest and shorter than the alternatives.

Choosing a creation strategy

PostgreSQL 15 added a STRATEGY option:

CREATE DATABASE myapp STRATEGY = wal_log;

wal_log is the default and is the better choice when the template database is small, which it almost always is. file_copy can be faster for a very large template, but it forces two checkpoints and can stall other work on the server.

Pass an invalid value and PostgreSQL tells you the options:

ERROR:  invalid create database strategy "bogus"
HINT:  Valid strategies are "wal_log" and "file_copy".

The createdb CLI exposes this as -S.

Listing and connecting

To list what exists, see PostgreSQL list databases.

To connect to the database you just made, see PostgreSQL connect to database. The short version:

\c myapp

Dropping a database

DROP DATABASE myapp;

To avoid an error if it does not exist:

DROP DATABASE IF EXISTS myapp;

From the shell, use dropdb:

dropdb myapp

PostgreSQL will not drop a database with active connections. If you need to force-drop it, terminate connections first:

-- Terminate all connections to the database
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'myapp' AND pid <> pg_backend_pid();

-- Then drop it
DROP DATABASE myapp;

You cannot drop the database you are currently connected to. Connect to postgres (or another database) first, then drop the target.

Common errors

ERROR: permission denied to create database

Your role does not have the CREATEDB attribute. A superuser can grant it:

ALTER ROLE alice CREATEDB;

After that, the same CREATE DATABASE succeeds. Note this is a role attribute, not a GRANT.

ERROR: database "myapp" already exists

The database name is taken. Drop it first, or use one of the idempotent patterns above. There is no IF NOT EXISTS for CREATE DATABASE.

ERROR: CREATE DATABASE cannot run inside a transaction block

You wrapped it in BEGIN, or passed several statements to psql -c, which does the same thing implicitly. Run it on its own.

ERROR: source database "template1" is being accessed by other users

Something is connected to the template you are copying. Disconnect it, or use TEMPLATE template0.

ERROR: new collation (C) is incompatible with the collation of the template database (en_US.utf8)

Same cause as the encoding error below. The hint PostgreSQL gives is the fix: use the same collation as the template, or specify TEMPLATE template0.

ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)

You are trying to use a different encoding from template1. Specify TEMPLATE template0:

CREATE DATABASE myapp
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8'
    TEMPLATE template0;

ERROR: there is 1 other session using the database

You cannot drop a database with active connections. Use pg_terminate_backend (shown above) to clear them first.

ERROR: cannot drop the currently open database

Connect to a different database first, then run DROP DATABASE.

Quick reference