DbSchema | How to Create a Table in PostgreSQL?

Table of Contents

1. Introduction
2. Prerequisites
3. Understanding PostgreSQL Data Types
4. How to Create a Table in PSQL
5. How to Create a Table in DbSchema
6. Conclusion
7. References

Introduction

PostgreSQL, commonly known as “Postgres,” is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It is equipped with a powerful set of features such as full ACID compliance, robustness, and performance, which enable you to manage your data no matter how big or small the dataset. In addition, it has a strong reputation for data integrity and correctness.In this article, we will explore two methods for creating tables: using the psql command-line interface and using DbSchema, a visual database design tool.

Prerequisites

Before we start, make sure you have the following:

• PostgreSQL: Make sure you have it installed, and it’s running on your computer. This is the database system we will be using.
• psql: This is the command-line interface for PostgreSQL. Check that it’s installed, and you can access it from your terminal.
• DbSchema: This is a database diagram designer and query tool. It needs to be installed and connected to your PostgreSQL database.

For installation and establishing connection you can read our article PostgreSQL-How to create a database?

Understanding PostgreSQL Data Types

When creating tables in PostgreSQL, it’s important to understand the data types you’ll be working with. Different data types dictate what kind of data can be stored in each column. Here’s a brief overview:

Data Type	Description
__INTEGER`	Used for whole numbers.
__DECIMAL or __NUMERIC	Used for precise fixed-point numbers.
__REAL or __FLOAT	Used for floating-point numbers, which can have a decimal point.
__CHAR(n)`	Used for fixed-length character strings, __n` defines the string length.
VARCHAR(n)	Used for variable-length character strings, n is the maximum string length.
BOOLEAN	Used for true/false values.
DATE	Used for date values.
TIMESTAMP	Used for date and time values.

How to Create a Table in PSQL

psql is a terminal-based interface for PostgreSQL. Here’s how to create a table:

1. Open your terminal

2. Log into your PostgreSQL server

   psql -U username
   

   Replace username with the username of your PostgreSQL server

3. Switch to your database

   \c my_database
   

Replace my_database with the name of your database

4. Create a table

   This SQL command creates a table named students:

   CREATE TABLE students (
       id INTEGER PRIMARY KEY,
       name VARCHAR(100) NOT NULL,
       age INTEGER NOT NULL,
       grade CHAR(2)
   );
   

   This SQL command creates a students table with four columns: id (integer, primary key), name (string up to 100 characters, cannot be null), age (integer, cannot be null), and grade (fixed string of 2 characters, can be null).

5. Confirm the creation

   Use the \dt command to verify the table creation:

   \dt
   

This will print the list of all the tables that we have in our database.

How to Create a Table in DbSchema

DbSchema provides a graphical interface that makes database management more intuitive. Let’s delve into the steps to create a table:

1. Launch DbSchema: Start by opening the DbSchema application on your computer.

2. Connect to your PostgreSQL Database: To connect DbSchema with your PostgreSQL database, click on the ‘Connect’ button. A dialog box will appear where you’ll be required to input your database credentials including the host, port, database name, username, and password. Click ‘Connect’ once you’ve filled in the necessary details.

3. Navigate to Schema Layout: After successfully connecting to your PostgreSQL database, you’ll be presented with the ‘Schema Layout’. This is a graphical representation of your database structure where you can visualize and manage your tables, views, and relationships.

4. Create a New Table: To create a new table, right-click anywhere in the Schema Layout panel and select ‘New Table’ from the context menu. A dialog box titled ‘Table’ will appear.

5. Define Table Structure: In the ‘Table’ dialog box, you’ll see fields to input your table name and columns. Here’s how to fill out these fields:

6. Table Name: Input the name of your new table in the ‘Name’ field. Let’s stick with ‘students’ for consistency.

7. Columns: Click on ‘Add Column’ for each new column you want to create. For each column, you need to specify the name, data type, size, and whether it’s a primary key, not null, or unique. Refer to the Understanding PostgreSQL Data Types section if you need a reminder of the different data types.

For our ‘students’ table, we’ll add the following columns:

- `id`: Set data type as 'SERIAL', tick the 'PK' (Primary Key) box, and ensure 'Not Null' is checked.
- `name`: Set data type as 'VARCHAR', set size as '100', and ensure 'Not Null' is checked.
- `age`: Set data type as 'INTEGER', and ensure 'Not Null' is checked.
- `grade`: Set data type as 'CHAR', set size as '2'.

A primary key is a column (or set of columns) in a table that uniquely identifies the rows in that table.
In the context of our students table, we defined id as the primary key. This means that each student will have a unique id value that cannot be null and remains the same once assigned. This unique id can be used to reliably identify each student in the table.

8. Save and Apply the Changes: Click on ‘Apply Changes’ to save your table structure and create the table in the database.

9. Verify Table Creation: To ensure the table has been created successfully, navigate to the ‘Database’ menu at the top, select ‘Refresh’, and then ‘Refresh All’. The new ‘students’ table should now be visible in your Schema Layout.

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.

Create Table

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

---

Add Table Columns

Add the columns to the table.

---

Conclusion

PostgreSQL is a powerful tool for managing and interacting with your data. Whether you prefer writing SQL commands in PSQL or designing your database visually in DbSchema, creating a table is a fundamental task that you’ll frequently perform. Both methods offer their own unique benefits. PSQL offers more control and is great for those who prefer a hands-on, command-line approach. In contrast, DbSchema provides a more visual and user-friendly interface, which can be easier for beginners and useful for complex database designs.

Regardless of the tool you choose, understanding the basics of table creation in PostgreSQL, such as defining columns, data types, and constraints, will be invaluable in your journey to mastering database management.

References

1. PostgreSQL Official Documentation: https://www.postgresql.org/docs/
2. DbSchema Documentation: https://www.dbschema.com/documentation/
3. SQL Syntax: https://www.w3schools.com/sql/
4. PostgreSQL Tutorial: https://www.postgresqltutorial.com/
5. DbSchema Interactive Diagrams: https://www.dbschema.com

Remember, the official documentation for both PostgreSQL and DbSchema is the most reliable source for up-to-date information. These resources can provide more in-depth knowledge and cover other complex aspects of creating and managing databases.

---