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(javatpoint), 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 javatpoint database, which we created earlier in the PostgreSQL tutorial.
Step2
- For connecting a database, we will enter the below command:
\c javatpoint 
Step3
- Now, we will enter the below command to create a table in the javatpoint 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 javatpoint 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.
| Parameter | Description |
|---|---|
| If not exists | If a table already occurs with a similar name, a warning will be displayed in place of an error. |
| Unlogged | This parameter does not enter data into the write-ahead log (WAL) because of the deletion of this further IO operation, write performance is improved. |
| Of_type_name | In this parameter, a table can have structure from the defined composite type. |
| Temporary or Temp | It is used to generate a temporary table, and it will delete after the existing operation or at the end of a session. |
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:
Leave a Reply