DbSchema Database Designer

DbSchema | How to Create a Table in PostgreSQL?

Publish on DbSchema Blog >>>

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


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.


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

  1. Create a table

    This SQL command creates a table named students:

    CREATE TABLE students (
        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).

  2. Confirm the creation

    Use the \dt command to verify the table creation:


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.


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.


  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.

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

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

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.