PostgreSQL - How to Create a Database?

---

PostgreSQL • 11-May-2023

---

How to Create a Database using psql and DbSchema

This article will guide you through the process of creating a database using psql and DbSchema. By the end, you’ll have a better understanding of how to manage your databases using these tools. The article is divided into the following sections:

Table of Contents

1. Introduction
2. Prerequisites
3. Installing PostgreSQL and psql
4. Creating a Database using psql
5. Installing DbSchema
6. Creating a Database using DbSchema
7. Designing the Database Schema
8. Generating SQL Scripts
9. Executing SQL Scripts
10. Conclusion

Introduction

In this article, we’ll explore both tools and show you how to create a database using psql, a Postgres command-line client, and DbSchema, a visual database designer.

Prerequisites

Before we begin, ensure that you have the following:

• A computer with a compatible operating system (Windows, macOS, or Linux)
• Administrative rights to install software on your computer
• A stable internet connection

Installing PostgreSQL and psql

First, you’ll need to install PostgreSQL and its command-line interface, psql. Follow the instructions for your operating system below:

1. Download the Windows / MacOS / Linux installer.
2. Run the installer and follow the on-screen instructions.
3. Ensure that the “Command Line Tools” option is selected during installation.

   ---

After installation, open a terminal or command prompt and verify that psql is accessible by running psql --version. You should see the version number of your installed PostgreSQL instance.

psql --version
psql (PostgreSQL) 14.1

Creating a Database using psql

To create a new database using psql, follow these steps:

1. Open a terminal or command prompt.

2. Connect to your PostgreSQL server using the following command, replacing username with your PostgreSQL username:

   postgres=# psql -U username

3. When prompted, enter your PostgreSQL password.

4. To create a new database, run the following command, replacing your_database_name with your desired database name:

   postgres=# CREATE DATABASE your_database_name;

5. Verify that the database was created by running:

   postgres=# \l
       List of Databases
   Name      | Owner
   ==================
   postgres  | postgres
   template0 | postgres 
   test      | postgres

   This will display a list of all databases in your PostgreSQL instance.

6. Exit psql by running:

   postgres=# \q

   Congratulations! You’ve just created a new database using psql.

Create Tables and Visually Manage PostgreSQL using DbSchema

DbSchema is a PostgreSQL client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Connect to Postgres

Start the application and connect to the Postgres database. Right-click the table folder to create a table.

---

Create a Database

Choose the database host, enter the username and password. Choose to create a database.

---

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams.

---

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected.

---

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys.

---

Query Builder

Create SQL Queries using the mouse.

---

SQL Query Editor

Edit and execute SQL Queries

---

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model.

---

Dark Theme

Configurable styles & dark theme.

---

Conclusion

In this article, we covered the process of creating a PostgreSQL database using both psql and DbSchema. You should now have a better understanding of how to manage your databases using these powerful tools. Whether you prefer the command-line interface of psql or the visual design capabilities of DbSchema, both methods will allow you to create and manage your databases effectively.

As you continue to work with PostgreSQL, you may want to explore additional features of psql and DbSchema, such as creating tables, defining relationships, or managing your schema. The official PostgreSQL documentation and the DbSchema documentation are great resources for learning more about these tools and the many features they offer.