How to Create a Database and Tables in PostgreSQL using pgAdmin 4

 

How to Create a Database and Tables in PostgreSQL using pgAdmin 4

If you are just starting your journey into data analysis or backend development, learning how to manage a database is your first major milestone. PostgreSQL is one of the most powerful, open-source relational databases in the world, and pgAdmin 4 is the most popular graphical interface used to manage it.

In this guide, I’ll walk you through the step-by-step process of creating your first database and defining your first table without writing a single line of code (though I’ll show you the SQL shortcut at the end!).

---

Prerequisites

Before we begin, ensure you have:

1. PostgreSQL installed on your system.

2. pgAdmin 4 (usually bundled with the PostgreSQL installation).

---

Step 1: Create a New Database

Think of a database as a large container that holds all your project's information.

1. Open pgAdmin 4 and connect to your server (you may need to enter your superuser password).

2. In the left-hand Browser panel, right-click on Databases.

3. Select Create > Database....

4. In the Database field, type a name (e.g., sales_management).

   • Tip: Use lowercase and underscores for better compatibility.

5. Click Save.

---

Step 2: Create a New Table

Now that we have a container, we need a structure to hold data. We’ll create a simple employees table.

1. Expand your new database (sales_management) in the Browser tree.

2. Navigate to Schemas > public.

3. Right-click on Tables and select Create > Table....

4. Under the General tab, name your table employees.

---

Step 3: Define Your Columns

A table is useless without columns. This is where we define what kind of data we are storing.

1. Click on the Columns tab in the Create Table window.

2. Click the + button to add the following columns:

   • emp_id: Set Data Type to serial (this auto-increments) and toggle Primary Key to Yes.

   • first_name: Set Data Type to character varying (or varchar).

   • hire_date: Set Data Type to date.

   • salary: Set Data Type to numeric.

3. Click Save.

---

The Pro Shortcut: Using SQL

If you prefer speed, you can click the Query Tool icon at the top of pgAdmin and run this script:

SQL

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    hire_date DATE,
    salary NUMERIC
);

---

Conclusion

Congratulations! You’ve successfully built the structure for your data. Creating databases and tables in pgAdmin is an essential skill for any data analyst or developer.

Watch the full video tutorial here: Watch the tutorial on YouTube Video]