Tables in any database, including PostgreSQL, are used to organize or summarize the 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 table is created, you can 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)?

Execute the “CREATE TABLE” command from the SQL SHELL for creating a table in PostgreSQL.

Syntax

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: Table name must be unique.

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

Generates a temporary table.

Unlogged

Used to create an unlogged table. It doesn’t specify the data in the write-ahead log.

How does CREATE TABLE statement work in PostgreSQL?

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

Step 1: List Databases

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

\l

Step 2: Select Database

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 table

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

\d staff_details;

Step 6: Create Duplicate Table

Let’s recreate an existing table:

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

designation VARCHAR(40),

An error occurred “table/relation already exists”.

Step 7: Specify the “if not exists” parameter

This time, let's use the "if not exists" parameter to create the same table:

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

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

Step 1: Select 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

Right-click on the “public”, then left-click on the “create” 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, type "CREATE TABLE" in psql. To create a table using pgAdmin, follow the steps appropriately as described in this write-up. This post explained some table creation methods in PostgreSQL using relevant screenshots.