Kenhuang Academy
  • English

      • 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

      ParameterDescription
      OWNERDatabase owner
      TEMPLATETemplate database, defaults to template1
      ENCODINGCharacter encoding, e.g., UTF8
      LC_COLLATECollation order
      LC_CTYPECharacter classification
      TABLESPACETablespace
      ALLOW_CONNECTIONSWhether connections are allowed
      CONNECTION LIMITMaximum 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;