WEB DEVELOPER SITE
PHP7
 

PostgreSQL to create a table


PostgreSQL uses the CREATE TABLE statement to create a database table.

Syntax

CREATE TABLE The syntax is as follows:

CREATE TABLE table_name(
   Column1 datatype,
   Column2 datatype,
   Column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

CREATE TABLE is a keyword that tells the database system to create a data table.

The table name must be unique among other tables, sequences, indexes, views, or external table names in the same schema.

CREATE TABLE Creates a new, blank table in the current database that will be owned by the user who issued the command.

Each field in the table defines the data type as follows:

Instance

The following table was created with a table named COMPANY table with a primary key of ID and NOT NULL indicating that the field is not allowed to contain NULL value:

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY NOT NULL,
   NAME TEXT NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(50),
   SALARY REAL
);

Next we will create a table that will be used in later chapters:

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY NOT NULL,
   DEPT CHAR(50) NOT NULL,
   EMP_ID INT NOT NULL
);

We can use the \d command to see if the form was created successfully:

welookupsdb=# \d
            List of relations
  Schema | Name | Type | Owner
--------+------------+-------+-------- --
  public | Company | table | postgres
  public | Department | table | postgres
(2rows)

\d tablename View the table information:

welookupsdb=# \d Company
                  Table "public.company"
 Column | Type | Collation | Nullable | Default
---------+---------------+-----------+ ----------+---------
 Id | integer | | not null |
 Name | text | | not null |
 Age | integer | | not null |
 Address | character(50) | | |
 Salary | real | | |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)