PostgreSQL CREATE TABLE

by

CREATE TABLE
To create a new table in a PostgreSQL database, one can use the UI or can also use the PostgreSQL CREATE TABLE statement.

Syntax: 

CREATE TABLE table_name (
    column_1 data_type column_constraint,
    column_2 data_type column_constraint,
    ... );

Creating a TABLE using UI:

  • Select the database.
  • To get the catalogues and schema, left-click on the box type structure associated with the selected database. You will see catalogues and schema.
  • To get the public, left-click on the box type structure associated with the schema.
  • To get the table, left-click on the box type structure associated with the public.
  • Right-click on the selected table to get a new table line.
  • Click on the new table line to create the desired table.

A new TABLE is thus created.

Creating a TABLE using the Query tool:

  • Select the database.
  • To get the catalogues and schema, left-click on the box type structure associated with the selected database. You will see catalogues and schema.
  • To get the public, left-click on the box type structure associated with the schema.
  • To get the table, left-click on the box type structure associated with the public.
  • Left-click on the tool on the topmost line to get the Query tool.
  • Click on the query tool.
  • Put the “CREATE Table” query here.
  • Click on the “Play” button.

The query will thus be executed.

Example 1: Creating a table with NULL and NOT NULL column constraint. 

CREATE TABLE employment  
( id INT NOT NULL AUTO_INCREMENT,  
  state VARCHAR(100) NOT NULL,  
  rate REAL );

Explanation:
Column 1:
id: Name of Column 1.
INT: Datatype of the Column 1 which is Integer.
NOT NULL: It cannot contain null values.
Column 2:
state: Name of Column 2.
VARCHAR: Datatype of Column 2 which also specifies a maximum limit of 100 characters in length for the “state”.
NOT NULL: It cannot contain null values.
Column 3:
rate: Name of Column 3.
REAL: Datatype of Column 3.
NULL: It is NULL by default and thus can contain null values.

Example 2: Creating a table with a PRIMARY KEY column constraint. 

CREATE TABLE employment  
( id INT NOT NULL AUTO_INCREMENT,  
  state VARCHAR(100) NOT NULL,  
  rate REAL,
  PRIMARY KEY(id) );

Explanation:
For distinguishing a unique row in a table the Primary Key Clause is used. Here “id” is defined as the Primary Key Column, thus every field of the primary key must contain NOT NULL values.