PostgreSQL Create Table

In PostgreSQL, the Create table command is used to create a new table in any of the given databases.

In this section, we are going to learn how we can create a table in PostgreSQL.

Syntax of creating table in PostgreSQL

CREATE TABLE table_name(  
   column1 datatype,  
   column2 datatype,  
   column3 datatype,  
   .....  
   columnN datatype,  
   PRIMARY KEY(one or more columns )  
);  

Here,

In the above syntax, the CREATE TABLE is a keyword, which used the database system for creating a new table.

table_name: It is used to define the name of the table.

Column1, Column2.... Column N: These are used to define the name of the columns.

data_type: It is used to define the data types (integer, text, character, Real, and so on) of the column.

Note: A table cannot have a similar name as any existing table in the same schema.

Creating a table in PostgreSQL

In PostgreSQL, we can create a table in two ways:

• PostgreSQL Create Table using pgAdmin
• PostgreSQL Create Table using SQL Shell

PostgreSQL Create Table using pgAdmin

We are going to follow the below process to create a table in pgAdmin:

Step1

• Firstly, we will open the latest version pgAdmin in our local system, and we will go to the object tree and select the database, in which we want to create a table.

Step2

• After that, left-click on the selected database(TheDeveloperBlog), and then we can see the Catalogs and Schemas.

Step3

• Then we will right-click on the Public under Schema section, select Create option from the given drop-down, and click on the Table from the given list.

Step4

• Once we click on the Table, the Create-table window will appear on the screen where we will enter all the necessary details like Table name. In our case, we will create a table called Employee.

Step5

• After that, we will move to the Column tab in the same window then click on the + sign to add columns in a particular table.
• And we can select the Data types from the given drop-down list as well as we can change the columns Not-null preference and also set the Primary key.
• And then click on Save to complete the process of creating a table as we can see in the below screenshot:

• And we can see that the Employee table is created under the Table section.

PostgreSQL Create Table using psql:

We are going to follow the below process to create a table in psql:

Step1

• Firstly, we will open the psql in our local system, and we will connect to the database where we want to create a table.
• We will create a table in the TheDeveloperBlog database, which we created earlier in the PostgreSQL tutorial.

Step2

• For connecting a database, we will enter the below command:

\c TheDeveloperBlog

Step3

• Now, we will enter the below command to create a table in the TheDeveloperBlog database.

create table Student(Stu_id int, Stu_Name text, Stu_Age int, Stu_address char(30));

• As we can see in the below screenshot that the table is created in TheDeveloperBlog database:

Step4

• We can use the below command to check the tables (relations) in a particular database.

Step5

• If we again try to create the same table, we will get the below error:

Step6

• We can use the parameter IF NOT EXISTS and we will get a Notice instead of an error:

In the below table, we can define some of the essential lists of parameters that we use while creating a table is in-depth.

The below example shows how we add constraints in a table:

Create table department
( dept_no int constraint dept_details_pk primary key 
  dept_name text NOT NULL,
Location varchar(15),
);

As we see in the below screenshot: