Continuation of Postgress Sql

5 min readJan 30, 2022

Before going through this I would recommend to go through my previous blog on Postgress Sql.

To get to know about shortcuts in Postgress we can enter to the command line and type help as shown in the below figure

If you want help in Sql commands type \h in the command line then you can get list of Sql commands supported by the Postgress.

If you want to know about the shortcuts in then type \? in the command line

Create Database:

In order to perform any database activity first and foremost thing is to create a database (even though we have a default database postgres ).

The command/query to create database is

CREATE DATABASE <dB name>

to list the databases type \l in the command line to see what are the databases available to us.

In the above figure I have been showing the default databases after when Postgress Sql is downloaded in the windows machine

Now we will be creating a new database say the database name is sample.

So our query now becomes:

CREATE DATABASE sample;

We can now see a new row is created when we typed \l.

After creating the database we should connect to the database which we created so that we could add tables in that database.

To connect to the database we could either use
\c <dB name> or connect <dB name>

If we specify \c <dB name> which is in the list of databases then we can connect to that database. else there would be an error.

successful connection

As we have created the database sample before this command so this resulted in success.

unsuccessful connection

DROP database:

I am assuming this is one of the rarest command ever used in production environments. So, lets take a scenario you are designing the database and some how you are not satisfied with the design and want to start from scratch. Then we use DROP database <dB name>.

After using the command the entire tables in the database and the database itself is removed. It is similar to rm -rf * in linux

CREATE TABLE :

After creating the database and connect to the database the next step we might do is create table. As we know table contains columns and rows. The columns are called attributes where as rows are called tuples.

When we use CREATE TABLE command we specify the attribute names, datatypes of attributes and do we need any constraint on that attribute.

CREATE TABLE < table name>(

attribute1_name datatype_of_attribute constraints_if_we_need,

attribute2_name datatype_of_attribute constraints_if_we_need,

attribute3_name datatype_of_attribute constraints_if_we_need,

attributek_name datatype_of_attribute constraints_if_we_need,

);

if we have k attributes in that table

Now we might get a doubt what are the datatypes supported by Postgress. Some of them i could mention but if you want in more detail once go through the official documentation of Postgress Sql

Some of them are:

bigserial — — autoincrement of 8 bit integers
smallserial — — autoincrement of 4 bit integers
cidr — — for IPV4 and IPV6 address
varchar(M) — — character length of maximum length “M”
text — — character length of infinity length
boolean — — True,False
uuid — — universally unique identifier

And the list goes on

Ok, now lets create our first table in sample database. Let’s say we create a table named ‘employee’ with attributes emp_id (which is generally integer type), emp_name (should be characters of some specific length) , date_of_birth here we can use either date or timestamp. Here we use date as the data type.

So the command looks like

CREATE TABLE employee(
emp_id INT,
emp_name VARCHAR(50),
date_of_birth DATE);

To know what tables are there in the database type\d in the command line.

To get description of the table \d <table name>

But this looks like a simple table with no constraints. In the above table we created we can create multiple rows without any data (like no emp_id, no emp_name, no date_of_birth)

So we put some constraints, like we create table registrations_table and lets say that attributes can be registration_id, name_of_person, date_of_birth, phone_number.

In general scenario the registration number should be auto increamented with each row, so the optimal data type is bigserial this should not be null, name_of_person should be varchar this should not be null, date_of_birth should be date this should not be null, phone_number can be null because we are assuming not every person has a mobile.