Table Creation in PostgreSQL

We have created database tech_db in previous article, now we will create tables in PostgreSQL database. Table creation in postgres is very much similar to creating table in any other RDBMS database, we can make use of data types described here

In oracle we use desc to see definition of any table in PostgreSQL, we use \d or \d+ to see list of columns and its description

We will create table name employee with few columns in it

CREATE TABLE employee (

emp_id int NOT NULL,

emp_name varchar(255) NOT NULL,

age int NOT NULL,

PRIMARY KEY(emp_id)

);

you can see, we have defined all columns as NOT NULL and primary key as emp_id, same can be defined as below as well

CREATE TABLE employee (

emp_id int NOT NULL PRIMARY KEY,

emp_name varchar(255) NOT NULL,

age int NOT NULL

);

List of tables can be fetched using \dt 

tech_db=# \dt

          List of relations

 Schema |   Name   | Type  |  Owner

--------+----------+-------+----------

 public | employee | table | postgres

(1 row)

tech_db=#

Create table using select as statement

create table emp_2019 as select * from employee where age>26;

List tables

tech_db=# \dt

          List of relations

 Schema |   Name   | Type  |  Owner

--------+----------+-------+----------

 public | emp_2019 | table | postgres

 public | employee | table | postgres

(2 rows)

tech_db=#

Create unlogged table
Unlogged tables are created with skipping write-ahead log, these are faster tables but can be recovered in case of crash and can not be replicated. syntax is similar to create table just with additional key word unlogged . for example

CREATE UNLOGGED TABLE student (

student_id int NOT NULL,

student_name varchar(255),

DOB date,

DOJ date

);

Describing table

tech_db=# \d employee;

            Table "public.employee"

  Column  |          Type          | Modifiers

----------+------------------------+-----------

 emp_id   | integer                | not null

 emp_name | character varying(255) | not null

 age      | integer                | not null

Indexes:

    "employee_pkey" PRIMARY KEY, btree (emp_id)

tech_db=#

tech_db=# \d+ employee;

                                Table "public.employee"

  Column  |          Type          | Modifiers | Storage  | Stats target | Description

----------+------------------------+-----------+----------+--------------+-------------

 emp_id   | integer                | not null  | plain    |              |

 emp_name | character varying(255) | not null  | extended |              |

 age      | integer                | not null  | plain    |              |

Indexes:

    "employee_pkey" PRIMARY KEY, btree (emp_id)

tech_db=#

Recommendation while creating tables

• use lower case name for tables
• use lower case name for columns

PostgreSQL column constraints

NOT NULL - value can not be null

UNIQUE - value must be unique

PRIMARY KEY - not null + unique

CHECK - enables to have check value before insert or update

REFERENCE - constraints the value of column in another table

PostgreSQL table constraints

Similar to column constraints just they are applied to whole table.