Skip to main content

CREATE DATABASE

Learning Focus

Use this lesson to understand CREATE DATABASE with PostgreSQL-native syntax, options, and safe workflows.

Concept Overview​

CREATE DATABASE is a Data Definition Language (DDL) command that creates a new database inside a PostgreSQL cluster.

PostgreSQL databases are isolated containers for schemas, tables, and other objects. You connect to exactly one database at a time.

Why is it important?​

  • Project isolation: keep unrelated apps/environments separated
  • Ownership and privileges: decide who can create objects and connect
  • Locale/encoding correctness: prevents subtle sorting/character issues later
  • Operational safety: sets a clean starting point for migrations and deployments

Where does it fit?​

Typical workflow:

  1. Create database (CREATE DATABASE)
  2. Connect to it (\c dbname in psql)
  3. Create schemas/tables (CREATE SCHEMA, CREATE TABLE)
  4. Grant access (roles/privileges)

Important PostgreSQL rule: CREATE DATABASE cannot run inside a transaction block.

Syntax & Rules​

Core Syntax​

CREATE DATABASE db_name;

With options:

CREATE DATABASE db_name
WITH
  OWNER = owner_role
  TEMPLATE = template0
  ENCODING = 'UTF8'
  LC_COLLATE = 'C'
  LC_CTYPE = 'C'
  CONNECTION LIMIT = 50;

Available Options / Parameters​

OptionExampleMeaning / Notes
OWNEROWNER = app_ownerdatabase owner (often an admin/migration role)
TEMPLATETEMPLATE = template0base database to copy from; template0 is minimal and safe for locale/encoding changes
ENCODINGENCODING = 'UTF8'character encoding; must be compatible with template
LC_COLLATELC_COLLATE = 'en_US.utf8'collation rules (sorting/comparison)
LC_CTYPELC_CTYPE = 'en_US.utf8'character classification rules
TABLESPACETABLESPACE = fast_tswhere the database files live (requires privilege)
CONNECTION LIMITCONNECTION LIMIT = 50soft limit; -1 means unlimited
IS_TEMPLATEIS_TEMPLATE = trueallows use as a template (admin use)

Key Rules and Considerations​

  • No IF NOT EXISTS: PostgreSQL does not support CREATE DATABASE IF NOT EXISTS.
  • Must run outside transactions: CREATE DATABASE errors inside BEGIN/COMMIT.
  • Encoding/locale are difficult to change later: choose deliberately at creation time.
  • You need CREATEDB privilege or be a superuser to create a database.
  • Database names are identifiers: use lowercase with underscores for consistency.

Connecting and Verifying in psql​

\l
\c db_name

Step-by-Step Examples​

Example 1: Create a Database (Beginner)​

CREATE DATABASE app_db;

Expected output:

CREATE DATABASE

Explanation:

  • Creates an empty database named app_db using default options.

Example 2: Create a Database With an Owner and Connection Limit (Intermediate)​

CREATE DATABASE app_db
WITH
  OWNER = app_owner
  CONNECTION LIMIT = 25;

Expected output:

CREATE DATABASE

Explanation:

  • Setting OWNER helps ensure the correct role owns future objects created by migrations.
  • CONNECTION LIMIT can protect small environments from accidental connection storms.

Example 3: Create a Database With a Specific Encoding/Locale (Advanced)​

CREATE DATABASE reports_db
WITH
  TEMPLATE = template0
  ENCODING = 'UTF8'
  LC_COLLATE = 'C'
  LC_CTYPE = 'C';

Expected output:

CREATE DATABASE

Explanation:

  • template0 is commonly used when you need non-default locale/encoding settings.
  • Locale choices affect ordering and text comparisons.

Example 4: Connect and Confirm It Exists (Beginner)​

\l
\c app_db

Expected output (example):

                                     List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  | Access privileges
-----------+----------+----------+---------+---------+-------------------
 app_db    | app_owner| UTF8     | C       | C       |
 postgres  | postgres | UTF8     | C       | C       |
 template0 | postgres | UTF8     | C       | C       | =c/postgres      +
 template1 | postgres | UTF8     | C       | C       | =c/postgres      +
(4 rows)

You are now connected to database "app_db" as user "postgres".

Explanation:

  • \l lists databases.
  • \c changes the active connection database in psql.

Practical Use Cases​

1) Create Environment Databases (dev/staging/prod)​

Context: isolate environments and permissions.

CREATE DATABASE myapp_dev  WITH OWNER = myapp_admin;
CREATE DATABASE myapp_stg  WITH OWNER = myapp_admin;
CREATE DATABASE myapp_prod WITH OWNER = myapp_admin;

2) Create a Database for Reporting With Different Collation​

Context: maximize stable comparisons/sorts for analytical workloads.

CREATE DATABASE myapp_reports
WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C';

3) Limit Connections in Small Shared Instances​

Context: avoid accidental exhaustion of connection slots.

CREATE DATABASE sandbox
WITH OWNER = sandbox_owner CONNECTION LIMIT = 10;

4) Create a Template Database (Admin)​

Context: seed a pre-configured database for faster provisioning.

CREATE DATABASE my_template WITH IS_TEMPLATE = true;

Common Mistakes & Troubleshooting​

Mistake 1: Running Inside a Transaction​

Wrong:

BEGIN;
CREATE DATABASE app_db;
COMMIT;

What happens (typical error):

ERROR:  CREATE DATABASE cannot run inside a transaction block

Fix: run CREATE DATABASE outside any transaction.

Mistake 2: Expecting IF NOT EXISTS​

Wrong:

CREATE DATABASE IF NOT EXISTS app_db;

What happens (typical error):

ERROR:  syntax error at or near "IF"

Fix options:

-- Option A: check first
SELECT 1 FROM pg_database WHERE datname = 'app_db';

-- Option B: handle existence at the deployment tool level

Mistake 3: Locale/Encoding Mismatch With the Template​

Symptom: ERROR: new encoding ... is incompatible with the encoding of the template database.

Fix: use TEMPLATE = template0 when you need a different locale/encoding.

Debugging tips:

  1. Confirm privileges: do you have CREATEDB or superuser?
  2. List existing databases with \l and check encoding/collation.
  3. If creation fails due to template mismatch, switch to template0.
  4. Standardize database creation in automation so settings are consistent.

Best Practices​

  • ✅ Decide encoding/collation intentionally; ❌ rely on defaults without understanding locale impact.
  • ✅ Use a dedicated owner role for migrations; ❌ create production databases owned by random user accounts.
  • ✅ Standardize naming conventions (lowercase, underscores); ❌ mix cases and special characters.
  • ✅ Automate DB creation consistently across environments; ❌ create databases manually with drifting options.
  • ✅ Keep connection limits sensible for shared instances; ❌ leave unlimited connections on tiny hosts.

Hands-On Practice​

Exercise 1 (Easy): Create a database​

Task: Create a database named practice_db.

-- Your SQL here

Solution:

CREATE DATABASE practice_db;

Exercise 2 (Medium): Create a database with an owner​

Task: Create practice_db2 owned by practice_owner.

-- Your SQL here

Solution:

CREATE DATABASE practice_db2 WITH OWNER = practice_owner;

Exercise 3 (Advanced): Create with template0 and locale​

Task: Create practice_reports using template0 and LC_COLLATE = 'C', LC_CTYPE = 'C'.

-- Your SQL here

Solution:

CREATE DATABASE practice_reports
WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C';

Connection to Other Concepts​

ConceptWhy it matters
Roles and privilegesdetermine who can connect/create objects
Schemasorganize objects inside a database
CREATE TABLEnext step after database creation
Backup and recoverydatabase creation is the start of lifecycle management
Transactionssome DDL cannot run inside a transaction in PostgreSQL

Visual Learning Diagram​

flowchart TD
  A[PostgreSQL Cluster] --> B[CREATE DATABASE]
  B --> C[Database: app_db]
  C --> D[Schemas]
  D --> E[Tables / Views / Functions]
  C --> F[Roles + Privileges]
  C --> G[Encoding + Collation]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G allNodes
  class B highlight

Common Pitfalls​

PitfallConsequencePrevention
Creating with the wrong localeunexpected sort/comparison behaviorstandardize locale at creation
Assuming IF NOT EXISTS existsdeployment failurescheck pg_database or handle in tooling
Running in a transactionimmediate errorrun outside BEGIN/COMMIT
Wrong ownerprivilege pain laterset OWNER explicitly
Manual one-off creationdrifting settingsautomate database provisioning

Quick Reference​

CREATE DATABASE app_db;
CREATE DATABASE app_db WITH OWNER = app_owner;
CREATE DATABASE reports_db WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C';
\l
\c app_db

What's Next​