CREATE DATABASE

Create, configure, and drop PostgreSQL databases.

Overview

A PostgreSQL server can host multiple independent databases, each with its own tables, schemas, roles, and settings. You create a new database with the CREATE DATABASE statement. By default, the new database is cloned from the template1 template, which contains the default configuration including standard extensions and settings. Each database has an owner (a role), a character set encoding (usually UTF8), and a locale (collation and character type). Choosing UTF8 encoding is strongly recommended for any application with international text. The locale affects sorting and case-folding for example, whether "ä" sorts before or after "b". Connections to a PostgreSQL server always target a specific database you cannot query across databases in a single SQL statement (use foreign data wrappers or logical replication for cross-database access). This isolation is a deliberate design choice that simplifies security and backup.

Key Points

•CREATE DATABASE name; creates a new database (runs as a top-level statement, not inside a transaction)
•New databases are cloned from template1 by default
•Specify ENCODING 'UTF8' to ensure proper international character support
•Only superusers or users with the CREATEDB privilege can create databases
•DROP DATABASE is irreversible it deletes all data in the database immediately
•You cannot drop the database you are currently connected to

Examples

Create a simple database

CREATE DATABASE myapp;

Create a database with explicit options

CREATE DATABASE myapp
  OWNER = appuser
  ENCODING = 'UTF8'
  LC_COLLATE = 'en_US.utf8'
  LC_CTYPE = 'en_US.utf8';

Encoding and locale must match the template database unless TEMPLATE = template0 is specified.

Drop a database

-- Must be connected to a different database first
DROP DATABASE myapp;

-- Only if it exists (avoids error)
DROP DATABASE IF EXISTS myapp;

CREATE DATABASE

Overview

Key Points

Examples

Exercises

Knowledge Check

Can CREATE DATABASE be run inside a transaction block?