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.

To avoid an error if the database already exists, use IF NOT EXISTS:

CREATE DATABASE IF NOT EXISTS myapp;

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.

| Template | Description | |----------|-------------| | template1 | Default template. Contains any extensions or objects you add to it. Copied when no template is specified. | | template0 | Clean baseline. Never modified. Use this when you need a known-clean database or want to set a custom encoding or locale. |

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;

Listing databases

In psql, use \l to list all databases:

\l

Or \l+ for more detail (size, tablespace, description):

\l+

From SQL, query the pg_database catalog:

SELECT datname, pg_encoding_to_char(encoding) AS encoding,
       datcollate, datctype, datconnlimit
FROM pg_database
ORDER BY datname;

To check whether a specific database exists:

SELECT EXISTS (
    SELECT 1 FROM pg_database WHERE datname = 'myapp'
);

Connecting to a database

In psql, switch databases with \c:

\c myapp

To connect as a different user:

\c myapp alice

From the shell:

psql -d myapp
psql -h localhost -p 5432 -U alice -d myapp

Using a connection string:

psql "postgresql://alice:password@localhost:5432/myapp"

Connection strings work with most PostgreSQL clients and drivers. The format is postgresql://user:password@host:port/database.

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 privilege. A superuser can grant it:

ALTER ROLE alice CREATEDB;

ERROR: database "myapp" already exists

The database name is taken. Either drop it first, or use IF NOT EXISTS to skip silently.

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

| Command | Description | |---------|-------------| | CREATE DATABASE name | Create a database with defaults | | CREATE DATABASE IF NOT EXISTS name | Create only if it does not exist | | CREATE DATABASE name OWNER role | Set the owner | | CREATE DATABASE name ENCODING 'UTF8' TEMPLATE template0 | Set encoding (requires template0) | | CREATE DATABASE name CONNECTION LIMIT n | Cap concurrent connections | | DROP DATABASE name | Delete a database permanently | | DROP DATABASE IF EXISTS name | Delete only if it exists | | \l | List databases in psql | | \c name | Connect to a database in psql | | createdb name | Create a database from the shell | | dropdb name | Drop a database from the shell |