Tables in any database, including PostgreSQL, are used to organize or summarize complex, detailed, and unordered data. To create a table in PostgreSQL, the first step is creating a database and selecting the desired one. Once a database is created, you can create a table of your choice and perform multiple operations on that table like insertion, deletion, searching, and updating. A table in PostgreSQL stores the data in a structured way, i.e. in the form of rows and columns.

In this write-up, you will learn how to create a table in PostgreSQL using pgAdmin, and SQL SHELL(psql).

How to Create a Table Using SQL SHELL(psql)?

You can execute/run the “CREATE TABLE” command from the SQL SHELL for creating a table in PostgreSQL. Here is the basic syntax for table creation using psql:

CREATE TABLE table_name(
first_column data_type,
second_column data_type,
third_column data_type,
.....
nth_column data_type
);

Here,

• “CREATE TABLE” is a statement that creates a table.
• table_name is a user-defined table’s name.
• first_column, second_column, and nth_column are the names of the columns.
• data_type represents the column type; it can be any type like int, String, char, etc.

Note: The table name must be unique and table columns must be separated using a comma.

What are the Parameters of the CREATE TABLE Command?

The CREATE TABLE statement can accept some parameters to perform different functionalities. The table below will illustrate some of the most commonly used parameters of the “CREATE TABLE” statement:

• If not exists: It shows a notice/warning instead of throwing an error.
• Temp/Temporary: It generates a temporary table.
• Unlogged: It is used to create an unlogged table. It doesn’t specify the data in the write-ahead log.

Let's go through the below-listed examples to create a table in PostgreSQL using SQL SHELL (psql).

Example 1: Create a New Table

Let’s execute the “\l” command to check the list of available databases:

\l

Step 2: Select Database

You can select the database by executing the "\c" command:

\c example

The command mentioned above will select the requested database, i.e., “example”:

Step 3: Create Table

CREATE TABLE staff_details(
id int PRIMARY KEY, 
name VARCHAR(40),
designation VARCHAR(50),

In the above-given table:

• CREATE TABLE is a predefined command to create a new table.
• staff_details is a user-defined table name.
• id, name, and designation are user-defined column names.
• int is a data type.
• PRIMARY KEY is a predefined reserved keyword that makes a column a unique identifier.
• VARCHAR is a character data type that stores limited characters.

Let’s execute this statement in SQL SHELL(psql):

Step 4: List of Tables

Let’s verify whether the table has been created or not. To do that, type the “\d” command:

\d

The above snippet verified that a table named “staff_details” had been created successfully.

Step 5: Describe the Created Table

Type the “\d” command followed by the table name to describe the details of a specific table:

\d staff_details;

Example 2: Create a Duplicate Table and Fix the “Relation Already Exists” Error

Let’s recreate an existing table:

CREATE TABLE staff_details(
id int PRIMARY KEY, 
name VARCHAR(40),
designation VARCHAR(40),

An error occurs stating that the “table/relation already exists”:

We can specify/use the "IF NOT EXISTS" parameter to fix the stated error:

CREATE TABLE IF NOT EXISTS staff_details(
id int PRIMARY KEY, 
name VARCHAR(40),
designation VARCHAR(40),

The above snippet verified that this time a notice/warning occurred instead of an error.

Example 3: Create a TEMP Table

The CREATE TABLE statement can be executed with the “TEMP” clause to create a table for the current session only. The temp/temporary table automatically dropped/deleted once the current session expired:

CREATE TEMP TABLE test_info(
test_id SERIAL PRIMARY KEY,  
std_name TEXT,
score INT);

A temporary table has been successfully created:

Note: A temporary/temp table can have the same name as a normal Postgres table.

This is how we can utilize the parameters along with the “CREATE TABLE” statement to achieve different functionalities.

How to Create a Table Using pgAdmin?

You can use the pgAdmin to create a table manually or using SQL queries. To create a table via Postgres queries, open the pgAdmin, enter your password, select the database, launch the query tool, and finally execute the CREATE TABLE command.

Follow the below-given procedure to create a table in PostgreSQL using pgAdmin (manually):

Step 1: Select the Database

Firstly, open the pgAdmin and select the desired database:

Choose the database where you want a table to be created.

Step 2: Select the Schema

Click on the “schemas” under the selected database:

Step 3: Create Table

Now right-click on the “public”, then left-click/hover on the “create” option, and finally click on the “Table...”, as shown in the below snippet:

Consequently, the following window will appear:

Step 4: Enter Table Details

Specify the table name under the “General” tab:

Now, open the columns tab to specify more details:

Click on the “+” sign to add a new row, insert the relevant details in each row, and finally click on the “Save” button.

Step 5: Resultant Output

Scroll down a little bit to reach the “tables” section. You will see that the specified table has been created successfully:

Click on the “SQL” tab to open the resultant query created for the “staff_details” table.

Conclusion

In PostgreSQL, a table can be created using SQL SHELL (psql) or pgAdmin. To create a table using SQL query, execute the "CREATE TABLE" command in psql or pgAdmin's Query tool. To create a table manually using pgAdmin, follow the steps appropriately as described in this write-up. This post has explained some table creation methods in PostgreSQL using relevant screenshots.