How to Create a Database from the Command Line Using Psql


By squashlabs, Last Updated: October 30, 2023

How to Create a Database from the Command Line Using Psql

Creating a database from the command line using psql is a straightforward process that allows you to quickly set up a new database for your application. In this guide, we will walk through the step-by-step instructions to create a database using psql.

Step 1: Accessing psql

Before you can create a database using psql, you need to access the psql command line interface. To do this, open your terminal or command prompt and enter the following command:

psql -U username

Replace username with your actual username. If you are using the default Postgres superuser, you can omit the -U flag.

Related Article: How to Check and Change Postgresql's Default Port

Step 2: Creating a Database

Once you are in the psql interface, you can create a new database by executing the following command:

CREATE DATABASE database_name;

Replace database_name with the desired name for your database. Make sure to avoid using spaces or special characters in the database name.

Step 3: Granting Permissions


Replace database_name with the name of your database and username with the username or role you want to grant access to.

Step 4: Verifying the Database

To verify that the database has been successfully created, you can list all the databases in your psql instance using the following command:


This will display a list of all databases, including the one you just created.

Related Article: How to Restore a Postgresql Backup File Using the Command Line

Step 5: Best Practices

When creating a database from the command line using psql, it is important to follow some best practices:

– Choose a descriptive and meaningful name for your database that reflects its purpose.
– Avoid using spaces or special characters in the database name to prevent any potential issues.
– Grant appropriate permissions to users or roles to ensure secure and controlled access to the database.
– Regularly backup your database to prevent data loss and ensure you can recover from any unforeseen issues.

Alternative Method: Using SQL File

In addition to creating a database directly from the psql interface, you can also create a database using an SQL file. This method can be useful when you want to automate the database creation process or when you need to create multiple databases with the same schema.

To create a database from an SQL file, follow these steps:

1. Create an SQL file (e.g., create_database.sql) with the following content:

CREATE DATABASE database_name;

Replace database_name with the desired name for your database.

2. Run the SQL file using the psql command:

psql -U username -f create_database.sql

Replace username with your actual username and create_database.sql with the path to your SQL file.

This method allows you to easily create databases in bulk or automate the creation process by executing the SQL file using a script or a batch file.

Tutorial: Managing PostgreSQL Databases with Vacuumdb

Managing PostgreSQL databases efficiently is crucial for optimal database management. This in-depth guide will help you understand and utilize the power of vacuumdb in... read more

How to Create a PostgreSQL Read Only User

Creating a read-only user in PostgreSQL database is an important step in securing your data. This article provides a guide on how to achieve this, covering topics such... read more

How to Check & Change the DB Directory in PostgreSQL

A detailed look at the functionality and application of postgresql-check-db-dir in PostgreSQL databases. This article explores the common queries used in PostgreSQL, how... read more

How to Disable IPv6 in PostgreSQL Databases

Disabling IPv6 in your PostgreSQL database setup is an important step to ensure optimal performance and security. This article provides a step-by-step guide on how to... read more

Step-by-Step Process to Uninstall PostgreSQL on Ubuntu

Uninstalling PostgreSQL from your Ubuntu system can be a process if you follow the step-by-step instructions provided in this article. From preparing for the... read more

Tutorial: Using Navicat for PostgreSQL Database Management

This article provides a detailed guide on using Navicat for PostgreSQL database management. Learn about data modeling, SQL queries, data migration, database... read more