DbSchema Database Designer

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

  1. Switch to your database

    \c my_database

    Replace my_database with the name of your database

  1. 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).

  1. 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.

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

  2. 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 >

Create Table

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


Add Columns >

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.


DbSchema → Your Trusted Partner in Database Design

Simplify complex database workflows and improve productivity with DbSchema's advanced design and management tools

Visual Design & Modeling
Visual Design & Schema Layout

➤ Create and manage your database schema visually through a user-friendly graphical interface.

➤ Easily arrange tables, columns, and foreign keys to simplify complex database structures, ensuring clarity and accessibility.

GIT & Collaboration
Version Control & Collaboration

➤ Manage schema changes through version control with built-in Git integration, ensuring every update is tracked and backed up.

➤ Collaborate efficiently with your team to maintain data integrity and streamline your workflow for accurate, consistent results.

Data Explorer & Query Builder
Relational Data & Query Builder

➤ Seamlessly navigate and visually explore your database, inspecting tables and their relationships.

➤ Build complex SQL queries using an intuitive drag-and-drop interface, providing instant results for quick, actionable insights.

Interactive Documentation & Reporting
HTML5 Documentation & Reporting

➤ Generate HTML5 documentation that provides an interactive view of your database schema.

➤ Include comments for columns, use tags for better organization, and create visually reports.