PostgreSQL - Getting Started PostgreSQL - Database PostgreSQL - Data Types PostgreSQL - CREATE Table PostgreSQL - DROP Table PostgreSQL - ALTER Table PostgreSQL - INSERT INTO Table PostgreSQL - SELECT FROM Table PostgreSQL - Select using WHERE clause PostgreSQL - WHERE clause with AND, OR and NOT operators PostgreSQL - UPDATE Table PostgreSQL - DELETE FROM Table PostgreSQL - Alias PostgreSQL - ORDER BY and LIMIT PostgreSQL - INNER JOIN PostgreSQL - LEFT JOIN

PostgreSQL - CREATE Table

PostgreSQL

← Prev Next →

In this tutorial we will learn to create tables in PostgreSQL.

What is a database table?

A database table is a collection of related data saved in tabular form in rows and columns.

We learned about create database in the previous tutorial. Feel free to check that out.

Create a table

To create a table in PostgreSQL we use the CREATE TABLE table_name command and pass the column name and data type.

Points to note:

Table contains a primary key denoted by the PRIMARY KEY.
To ensure that a column holds unique value we use UNIQUE constraint.
To ensure that a column in a row always get a value we use NOT NULL constraint.
To set a default value we use the DEFAULT 'value' constraint. So, if a value is not provided for the column then a default value will be used.
To set a default NULL value we use the NULL constraint.

PRIMARY KEY helps in uniquely identify a row in a table. No two rows in the table can have the same primary key value.

Employee Table

In the following example we will create employee table with 6 columns.

employeeId holds the employee ID and it is the PRIMARY KEY of the table.
firstName The first name of the employee.
lastName The last name of the employee.
score This holds the total points scored by the employee.
lastModifiedAt The date time when the employee details were last modified.
createdAt The date time when the employee details were created.

Alright, let's go ahead and create the employee table. Login to your local PostgreSQL server and connect to your database and run the following command.

CREATE TABLE employee (
  "employeeId" varchar(20) NOT NULL,
  "firstName" varchar(100) NOT NULL,
  "lastName" varchar(100) NOT NULL,
  score int NULL DEFAULT 0,
  "lastModifiedAt" timestamp NULL,
  "createdAt" timestamp NOT NULL,
  CONSTRAINT "employeeId_employee_pk" PRIMARY KEY ("employeeId")
);

In the above SQL query we are creating the employee table and setting the employeeId column as the primary key.

Let's walk through the columns of the employee table.

Column	Type	Description
employeeId	varchar(20)	This column can save variable length string of max 20 characters. The column has `NOT NULL` constraint so we must provide value.
firstName	varchar(100)	This column can save variable length string of max 100 characters. The column has `NOT NULL` constraint so we must provide value.
lastName	varchar(100)	This column can save variable length string of max 100 characters. The column has `NOT NULL` constraint so we must provide value.
score	int	This column can save integer value. The column has `DEFAULT 0` constraint. This means if value is not provided then default value of 0 is added.
lastModifiedAt	timestamp	This column can save date time value `'YYYY-MM-DD HH-MM-SS'`. Click here for Date time data type. The column has `NULL` constraint. This means it is optional.
createdAt	timestamp	This column can save date time value `'YYYY-MM-DD HH-MM-SS'`. The column has `NOT NULL` constraint so we must provide value.

Let's try to create another table and this time we will use the serial data type for the primary key of the table.

Comments Table

Let's create the comments table with the following columns.

commentId This is the ID of the comment and primary key of the table.
commentBody This is the comment body.
lastModifiedAt This is the date time when the comment was last modified.
createdAt This is the date time when the comment was created.

We will run the following SQL query to create the comments table.

CREATE TABLE "comments" (
  "commentId" serial NOT NULL,
  "commentBody" text NOT NULL,
  "lastModifiedAt" timestamp NULL,
  "createdAt" timestamp NOT NULL,
  CONSTRAINT "commentId_comments_pk" PRIMARY KEY ("commentId")
);

The commentId column is the primary key for the comments table. It is set to serial data type so, it will auto increment by itself.

The commentBody column will hold string value and is of text data type.

The lastModifiedAt and createdAt holds date time value.

← Prev Next →