Javatpoint Logo
Javatpoint Logo

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

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.
PostgreSQL Create Table

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.
PostgreSQL Create Table

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.
PostgreSQL Create Table

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:
PostgreSQL Create Table
  • And we can see that the Employee table is created under the Table section.
PostgreSQL Create Table

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:

PostgreSQL Create Table

Step3

  • Now, we will enter the below command to create a table in the javatpoint database.
  • As we can see in the below screenshot that the table is created in javatpoint database:
PostgreSQL Create Table

Step4

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

Step5

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

Step6

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

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:

As we see in the below screenshot:

PostgreSQL Create Table




Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA