PostgreSQL - Create Database

PostgreSQL - Creating a Database

In PostgreSQL, a database is a container for SQL objects like tables, views, and functions. You can create a new database using either an SQL command or a command-line utility. Both methods require appropriate user privileges (typically superuser or a user with the CREATEDB attribute).

Method 1: Using the SQL Command CREATE DATABASE

The CREATE DATABASE command is the standard SQL way to create a new database. You can run it from any SQL client, such as the psql interactive terminal.

Basic Syntax

CREATE DATABASE database_name;

Syntax with Common Options

In practice, you often want to specify additional parameters, like the database owner and encoding.

CREATE DATABASE my_app_db
WITH
    OWNER = my_app_user
    ENCODING = 'UTF8'
    CONNECTION LIMIT = 100;

• OWNER: Specifies the user role that will own the new database. This is a crucial security practice.
• ENCODING: Sets the character set for the database. UTF8 is the modern standard and highly recommended for supporting a wide range of characters.
• CONNECTION LIMIT: Sets the maximum number of concurrent connections allowed to this database.

Method 2: Using the createdb Command-Line Utility

createdb is a convenient wrapper around the CREATE DATABASE command that you can run directly from your system’s shell (not inside psql).

Syntax

createdb [option...] [dbname [description]]

Example

This command creates a new database named inventory_db, owned by the user inventory_manager, and connects to the PostgreSQL server on the localhost.

# Run this from your bash/zsh/cmd terminal, not inside psql
createdb -h localhost -p 5432 -U postgres -O inventory_manager -E UTF8 inventory_db

• -U postgres: Connect as the postgres user (or another superuser).
• -O inventory_manager: Set the owner of the new database to inventory_manager.
• -E UTF8: Set the encoding to UTF8.

Verifying Database Creation

After creating a database, you can verify its existence by listing all databases. Connect using psql and run the \l command.

postgres=# \l

                                  List of databases
     Name      |     Owner      | Encoding | Collate | Ctype |   Access privileges
---------------+----------------+----------+---------+-------+-----------------------
 my_app_db     | my_app_user    | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres      | postgres       | UTF8     | C.UTF-8 | C.UTF-8 |
 ...etc

Best Practices and Next Steps

• Principle of Least Privilege: Avoid using the postgres superuser for your applications. After creating a database, create a dedicated user role with only the necessary permissions for that database (CREATE ROLE..., GRANT...).
• Naming Conventions: Use lowercase letters, numbers, and underscores (_) for database names to avoid quoting issues and ensure cross-platform compatibility.
• Development vs. Production: In development, consider using tools like Docker Compose to automate the creation and setup of your database, ensuring a consistent environment for all developers.
• Schema Migrations: For real-world projects, use a schema migration tool (like Flyway, Liquibase, or one built into your ORM) to manage database changes programmatically. The initial CREATE DATABASE is just the first step.