PostgreSQL Create Database

Overview

In PostgreSQL, a database is the top-level container for storing data. Creating a database is the first step in using PostgreSQL. This chapter explains how to create databases using SQL commands and command-line tools.

CREATE DATABASE Statement

Basic Syntax

CREATE DATABASE database_name;

Simple Examples

-- Create a simple database
CREATE DATABASE myapp;

-- Create database with owner
CREATE DATABASE myapp OWNER postgres;

-- Create database with encoding
CREATE DATABASE myapp ENCODING 'UTF8';

Complete Syntax

CREATE DATABASE database_name
    [WITH]
    [OWNER = role_name]
    [TEMPLATE = template]
    [ENCODING = encoding]
    [LC_COLLATE = lc_collate]
    [LC_CTYPE = lc_ctype]
    [TABLESPACE = tablespace_name]
    [ALLOW_CONNECTIONS = true | false]
    [CONNECTION LIMIT = connlimit]
    [IS_TEMPLATE = true | false];

Parameter Description

Parameter	Description
OWNER	Database owner
TEMPLATE	Template database, defaults to template1
ENCODING	Character encoding, e.g., UTF8
LC_COLLATE	Collation order
LC_CTYPE	Character classification
TABLESPACE	Tablespace
ALLOW_CONNECTIONS	Whether connections are allowed
CONNECTION LIMIT	Maximum connections, -1 for unlimited

Using Command Line Tools

createdb Command

# Create database
createdb myapp

# Specify owner
createdb -O postgres myapp

# Specify encoding
createdb -E UTF8 myapp

# Specify user and host
createdb -U postgres -h localhost myapp

# Create with comment
createdb myapp "My Application Database"

Using psql

# Execute SQL via psql
psql -U postgres -c "CREATE DATABASE myapp;"

# Create after entering psql
psql -U postgres
postgres=# CREATE DATABASE myapp;

Practical Examples

Creating Development Environment Databases

-- Create development database
CREATE DATABASE myapp_dev
    OWNER = developer
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8';

-- Create test database
CREATE DATABASE myapp_test
    OWNER = developer
    TEMPLATE = myapp_dev;

-- Create production database
CREATE DATABASE myapp_prod
    OWNER = admin
    CONNECTION LIMIT = 100;

List All Databases

-- Using SQL query
SELECT datname FROM pg_database;

-- Or using psql command
\l

Important Notes

1. Naming conventions: Database names should use lowercase letters, numbers, and underscores
2. Permissions required: Requires CREATEDB privilege or superuser rights
3. Encoding choice: UTF8 encoding is recommended for multi-language support
4. Template database: Defaults to copying from template1, can specify other templates

Common Issues

Database Already Exists

-- Use IF NOT EXISTS to avoid errors
CREATE DATABASE IF NOT EXISTS myapp;

-- Or check before creating
SELECT datname FROM pg_database WHERE datname = 'myapp';

Connection Limit

-- Create database with connection limit
CREATE DATABASE limited_db CONNECTION LIMIT 10;

-- Modify existing database connection limit
ALTER DATABASE myapp CONNECTION LIMIT 50;