Creating Databases

A single PostgreSQL server can host many independent databases. Each one has its own set of tables, schemas, and permissions — data in one database cannot be directly queried from another in the same query, which makes databases a natural boundary between separate applications or environments on the same server.

Creating a database

SQL

CREATE DATABASE dbname;

That is the entire command. Once it runs, the new database exists immediately and is ready to accept tables, though it starts out empty aside from the default public schema (covered on the next page).

Connecting to a specific database

From psql, switch into the new database with the \c meta-command, or reconnect from the shell with -d:

SQL

\c dbname

Bash

psql -U postgres -d dbname

Listing existing databases

From inside psql, the quickest way is the \l meta-command:

SQL

\l

Or, using plain SQL that works from any client, query the system catalog directly:

SQL

SELECT datname FROM pg_database;

Options at creation time

CREATE DATABASE accepts several optional settings you will occasionally need, most commonly the character encoding and locale:

Specifying encoding and owner

SQL

CREATE DATABASE dbname
    WITH OWNER = app_user
    ENCODING = 'UTF8'
    TEMPLATE = template0;

OWNER — which role owns (and by default, has full privileges over) the database
ENCODING — the character encoding used to store text; UTF8 is the sensible default for almost every project
TEMPLATE — the database this one is copied from; template0 is a pristine, unmodified template useful when you need a specific encoding or locale

Dropping a database

SQL

DROP DATABASE dbname;

This is irreversible

DROP DATABASE permanently deletes the database and every table, row, and index inside it — there is no undo and no recycle bin. Always double-check you are connected to the right server before running it, and make sure you have a backup if the data matters. PostgreSQL will also refuse to drop a database that still has active connections, until they are closed.

Tip

It is common practice to create a separate database per environment — for example app_development, app_test, and app_production — rather than reusing one database and relying on naming conventions for tables.