Home » Blog » Creating a PostgreSQL Database: CLI and SQL Guide

Information

Creating a PostgreSQL Database: CLI and SQL Guide

Benhcmark Administrator

01/09/2025

0 Views 0

SaveSavedRemoved 0

How to Create a PostgreSQL Database: A Step-by-Step Guide for CLI and SQL

PostgreSQL is a powerful, open-source object-relational database system known for its reliability, feature robustness, and performance. For developers and database administrators, creating a new database is a foundational skill. Whether you’re setting up a new application or organizing data for a project, understanding how to properly initialize a database is crucial.

This guide will walk you through the two primary methods for creating a database in PostgreSQL: using the command-line interface (CLI) and executing a direct SQL command.

Prerequisites

Before you begin, ensure you have the following:

• PostgreSQL installed on your system.
• Access to a command-line terminal or shell.
• Sufficient user privileges to create databases (typically a postgres user or a user with the CREATEDB attribute).

Method 1: Using the Command-Line Interface (CLI)

For many users, the quickest way to create a database is by using the createdb command-line utility. This tool is a simple wrapper around the CREATE DATABASE SQL command, allowing you to execute it directly from your terminal without logging into the PostgreSQL interactive prompt.

The Basic createdb Command

The most straightforward command requires just the name of the database you wish to create.

createdb your_new_database

After running this, PostgreSQL will create a new database named your_new_database. By default, the new database will be owned by the user who executed the command.

Specifying a Database Owner

It is often best practice to assign a specific owner to a database, especially in a multi-user environment. This helps manage permissions and security. You can do this with the -O (uppercase letter O) flag.

createdb -O username your_new_database

In this example, username will be the owner of your_new_database. This user must already exist within your PostgreSQL cluster.

Verifying Database Creation

To confirm that your database was successfully created, you can list all available databases using the psql command with the -l flag.

psql -l

This will display a table of all databases, their owners, encoding, and other details. You should see your_new_database in this list.

Method 2: Using SQL Commands

The second method involves connecting to the PostgreSQL server and running the CREATE DATABASE SQL statement directly. This approach is more versatile and is the standard way to manage databases from within an application or a database administration tool like pgAdmin or DBeaver.

First, you need to log in to the PostgreSQL interactive terminal, psql.

psql -U postgres

(You may need to connect to an existing database like template1 or postgres to run commands.)

The Basic CREATE DATABASE Command

Once you are at the psql prompt, you can use the standard SQL command. Remember that all SQL statements must end with a semicolon (;).

CREATE DATABASE your_new_database;

This command functions similarly to the basic createdb utility, creating a database owned by the current user.

Advanced Creation with Options

The SQL method provides more granular control over the database configuration during creation. You can specify the owner, encoding, and connection limits all in one statement.

CREATE DATABASE your_new_database
OWNER username
ENCODING 'UTF8'
CONNECTION LIMIT 10;

Let’s break down these options:

• OWNER: Assigns a specific user role as the owner of the new database. This is a critical security practice.
• ENCODING: Sets the character set encoding for the database. UTF8 is the recommended standard for most modern applications as it supports a wide range of characters.
• CONNECTION LIMIT: Restricts the number of concurrent connections that can be made to this specific database. This is useful for resource management.

Security and Best Practices for Database Creation

Creating a database is simple, but following best practices will ensure your system is secure, scalable, and easy to maintain.

1. Use Dedicated Owners: Avoid using the default postgres superuser as the owner for your application databases. Create specific user roles with limited privileges and assign them as owners. This principle of least privilege is a core security concept.
2. Standardize Naming Conventions: Adopt a consistent naming scheme for your databases. A common convention is to use lowercase letters, numbers, and underscores (e.g., project_name_prod, project_name_dev). Avoid special characters or mixed case, which can cause issues with some tools and operating systems.
3. Choose the Right Encoding: While you can select different encodings, UTF8 is almost always the correct choice. Using the wrong encoding can lead to data corruption or errors when handling international characters.
4. Understand Templates: PostgreSQL creates new databases by cloning an existing template database. By default, it uses template1. Be careful not to modify template1 unless you want those changes to propagate to all future databases.

How to Drop (Delete) a Database

You may eventually need to delete a database. This action is irreversible and will permanently delete all data, tables, and objects within it.

Using the CLI:
The dropdb command is the counterpart to createdb.

dropdb your_new_database

Using SQL:
From within psql or another SQL client, use the DROP DATABASE command.

DROP DATABASE your_new_database;

Warning: Proceed with extreme caution when dropping a database. There is no confirmation prompt, and this action cannot be undone. Always ensure you have a recent backup before performing this operation on a production system.

Source: https://www.redswitches.com/blog/how-to-create-a-database-in-postgresql/