Basic Concepts of PostgreSQL

Contents:

• Introduction to PostgreSQL
• Data Definition Language ( DDL )
• Data Manipulation Language ( DML )
• Transaction Control Language ( TCL )
• Data Control Language ( DCL )

1. Introduction to PostgreSQL

PostgreSQL is an Object-relational Database Manegment System (ORDBMS) based on the INGRES ( INteractive Graphical REtrival System ) Package developed at the university of California , Berkeley. The POSTGRES ( Post Ingres ) Project started in 1985 , and version 1 was released to a small number of external user in june of 1989. PostgreSQL has become the most advanced open source database, available all over the world. PostgreSQL has become a popular database as a service (DBaaS) among the current clouds , such as Amazon Web Service (AWS) , Google Cloud SQL , Microsoft Azure , Heroku and EnterpriseDB Cloud. PostgreSQL supports a large parts of the SQL standard and offers many modern features: Complex Queries , Foreign keys , triggers , updateble views , trasactional integrity , multiversion concurrency control. PostgreSQL can be extended by the user in many ways, For Example by adding new data types , functions , operators , aggregate functions , index method and procedural languages . It is fully ACID ( Atomicity , Consistency , Isolation , Durability ) compliant which makes it an ideal choice for OLTP ( Online Transaction Processing ). It is also capable of performing database analytics. It can be integrated with mathematical software like Matlab and R.

2. PostgreSQL Commands

SQL commands are instructions used to communicate with the database to perform specific tasks that work with data SQL commands can be used not only for searching the database but also to perform various other functions like , For Example , you can create tables , add data to tables or modify data , drop the table , set permission for user. SQL commands are grouped into four major categories depending on their functionality:

1. Data Definition Language ( DDL ) : These SQL commands are used for creating , modifying and dropping the structure of database objects. The commands are CREATE , ALTER , DROP , RENAME and TRUNCATE.

2. Data Manipulation Language ( DML ) : These SQL commands are used for storing , retrieving , modifying and deleting data. These commands are SELECT , INSERT , UPDATE and DELETE.

3. Transaction Control Language ( TCL ) : These SQL commands are used for managing changes affecting the data. These commands are COMMIT , ROLLBACK , SAVEPOINT.

4. Data Control Language ( DCL ) : These commands are used for providing security to database objects. These commands are GRANT and REVOKE.

2.1. Data Definition Language ( DDL ) :

2.1.1 CREATE TABLE Statement :

The CREATE TABLE Statement is used to create tables to store data. Integrity constraints like primary key , unique key and foreign key can be defined for the columns while creating the table. The integrity constraints can be defined at column level or table level. The implementation and the syntax of the CREATE statement differs for different RDBMS

The Syntax for the CREATE TABLE Statement is :

CRETAE TABLE table_name

(Column_name1 datatype constraint,

Column _name2 datatype,…

Column_nameN datatype ) ;

• Table_name : is the mane of the table
• Column_name1 . Column_name2 : is the name of the columns
• Datatype: is the datatype for the column like char , date , numbers , etc

2.1.2 PostgreSQL DATATYPES :

• Character Datatype
• Numeric Datatype
• Binary Datatype
• Network Datatype
• Text Search Datatype
• Date/Time Datatype
• Boolean Datatype
• Geometric Datatype
• Enumerated Types
• Range Type
• UUID Type
• XML type
• JSON type
• Pesudo — Type

2.1.2.1 Character Datatype: PostgreSQL supports character datatype for storing text value. PostgreSQL builds character datatypes of the same internal structure PostgreSQL offers three character datatype : CHAR(n) , VARCHAR(n) and TEXT

2.1.2.2 Numeric Datatype : PostgreSQL supports two distinct types of numbers :

• Intergers
• Floating Point numbers

2.1.2.3. Binary datatype : A binary string is a sequency of octets or bypes. Binary Postgres data types are divided in two ways

• Binary string allow storing odd of the value zero
• Non- printable octets

2.1.2.4. Network Address Type :

Many applications store network information like IP address of the user or sensors. PostgreSQL has three native types which help you to optimize the network data

2.1.3. PostgreSQL Integrity Constraints :

Integrity Constraints are used to apply business rules for the database tables. The Constraints available in SQL are Foreign key , primary key , Not Null , unique , check constraint can be defined in two ways

1. The constraints can be specified immediately after the column definition. This is called Column-level definition
2. The constraints can be specified after all the column are defined. This is called table-level definition

2.1.3.1. Primary key :

The constraints defiend a column or combination of column which uniquely identifies each row in the table

Syntax to define a primary key at column level :

Column_name datatype [ CONSTRAINTS constraint_name ] PRIMARY KEY

Syntax to define a primary key at table level :

[ CONSTRAINTS Constraints_name ] PRIMARY KEY ( Column_name1 , Column_name2 , …)

Column_name1 , Column_name2 are the names of the column which define the primary key

The syntax within the brackets i.e [ CONSTRAINTS constraint_name ] is optional

2.1.3.2. Foreign Key or Referential Integrity :

This constraints identifies any column referencing the PRIMARY KEY in another table. It establishes a relationship between two column in the same table or between different table. For a column to be defined as a foreign key , it should be defined as a primary key in the table which it is foreign key

Syntax to defined a foreign key at column level :

[ CONSTRAINTS constraint_name ] FOREIGN KEY 9 ( column_name ) REFERENCES referenced_table_name ( column_name );

2.1.3.3. Not Null Constraints :

This constriants ensures all rows in the table contain a definite value for the column which is specified as not null which means a null is not allowed

Syntax to defined a Not Null Constraints :

[ CONSTRAINTS constraint_name ] NOT NULL

2.1.3.4. Unique key :

This constraints ensures that a column or a group of the column in each row have adistinct value. A column can have a null value but the value cannot be duplicated

Syntax to defined a unique key at column level :

[ CONSTRAINTS constraint_name ] UNIQUE

Syntax to defined a unique key at table level :

[ CONSTRAINTS constraint_name ] UNIQUE ( column_name )

2.1.3.5. Check Constraint :

This constraints defines a business rule on a column. All the rows must satisfy their rule. The constraint can be applied for a single coulmn or a group of the coulmns

Syntax to define a Check Constraints :

[ CONSTRAINTS constraint_name ] CHECK ( condition )

2.1.4 ALTER TABLE Statement

The SQL ALTER TABLE commands is used to modify the definition structure of the table by modifying the definition of the its column. The ALTER commands is used to perform the following function

1. Add , drop , modify table columns
2. Add and drop constraints
3. Enable and disable constraints

2.1.4.1 Syntax to ADD a column

ALTER TABLE table_name ADD column_name datatype ;

• FOR EXAMPLE : To add a column “ experience” to the employee table , the query would be like

ALTER TABLE employee ADD experience number ( 3 ) ;

2.1.4.2 Syntax to DROP a column

ALTER TABLE table_name DROP column_name ;

• FOR EXAMPLE : To drop the coulmn “ location” from the employee table , the query would be like

ALTER TABLE employee DROP locations ;

2.1.2.3 Syntax to MODIFY a column

ALTER TABLE table_name MODIFY coulmn_name datatype ;

• FOR EXAMPLE : To modify the column salary in the employee table , the query would be like

ALTER TABLE employee MODIFY ( salary number (15,2));

2.1.2.4 Syntax to ADD PRIMARY KEY Constraints

ALTER TABLE table_name ADD CONSTRAINTS constraint_name PRIMARY KEY column_name;

2.1.2.5 Syntax to DROP PRIMARY KEY Constraints

ALTER TABLE table_name DROP PRIMARY KEY ;

2.1.2.6 The DROP TABLE Statement

The DROP TABLE statement is used to delete a table.

DROP TABLE table_name ;

2.1.3 TRUNCATE TABLE Statement

What if we only want to delete the data inside the table and not the table itself ? Then , We use the TRUNCATE TABLE

TRUNCATE TABLE table_name ;

2.2 DATA MANIPULATION LANGUAGE ( DML ) :

2.2.1 The SELECT Statement

The SELECT statement is used to select data from a database. The result is stored in a result table called the result set

SELECT Syntax :

SELECT * FROM table_name ;

2.2.2 The SELECT DISTINCT Statement

In a table , some of the column may contain duplicate value. This is not a problem , however , sometimes you will want to list only the different (distinct) value in the table. The DISTINCT keywords can be used to return only distinct ( different ) value

SELECT DISTINCT Syntax :

SELECT DISTINCT column_name(s)

FROM table_name ;

2.2.3 The WHERE Clause

The WHERE clause is used to extract only those records that fulfill a specified criterion

WHERE Syntax :

SELECT column_name(s)

FROM table_name

WHERE column_name operator value ;

2.2.4 The AND & OR Operators

• The AND operator : display a record if both the first condition and the second condition is true
• The OR operators : display a record if either the first condition or the second condition is true

2.2.5 The ORDER BY Clause

The ORDER BY Clause is used to sort the result set by specified column

The ORDER BY Clause sort the record in ascending order by default

If you want to sort the record in a descending order , you can use the DESC keyword

ORDER BY Syntax :

SELECT column_name(s)

FROM table_name

WHERE column_name operator

value GROUP BY column_name(s) ;

2.2.5 The HAVING Clause

The HAVING Clause can be used to restrict the display of grouped rows. The result of the grouped query is passed on the HAVING Clause for output filtration

HAVING Syntax :

SELECT column_name(s)

FROM table_name

WHERE column_name operator

value GROUP BY column_name(s)

HAVING condition ;

2.2.6 The INSERT INTO Statement

The INSERT INTO Statement is used to insert a new row in a table

SQL INSERT INTO Syntax :

It is possible to write the INSERT INTO statement in two forms

1. The first form doesn’t specify the column name where the data will be inserted , only their values :

INSERT INTO table_name VALUES ( value1, value2, value3, ..);

OR

INSERT INTO table_name (&coulmn1, &column2 , &column3,..)

2. The second form specifies both the column names and the values to be inserted :

INSERT INTO table_name ( column1,column2,..) VALUES (values1,value2,..);

2.2.6 The UPDATE Statement

The UPDATE Statement is used to update existing records in a table

SQL UPDATE Syntax :

UPDATE table_name

SET column1=value

column2=value2,..WHERE

some_column=some_value ;

2.2.7 The DELETE Statement

The DELETE Statement is used to delete rows in a table

SQL DELETE Syntax :

DELETE FROM table_name WHERE

some_column=some_value;

2.3 TRANSACTION CONTROL LANGUAGE ( TCL )

Transaction Control Language ( TCL ) command are used to manage transaction in database. These are used to manage the changes made by DML statement. It is also allows statement to be grouped together into logical transaction

2.3.1 COMMIT Command :

Commit command is used to permanently save any transaction into database. Following is commit command Syntax

Commit ;

2.3.2 ROLLBACK Command :

This commands restores the database to last committed state. It is also use with savepoint command to jump to a savepoint in a transaction

Rollback command Syntax :

rollback to savepoint_name ;

2.3.3 Savepoint Command :

savepoint command is used to temporarily save a transaction so that you can rollback to that point whenever necessary

Savepoint command Syntax :

Savepoint Savepoint_name ;

2.4 DATA CONTROL LANGUAGE ( DCL )

Data control language ( DCL ) is used to control privilege i database. To perform any operation in the database , such as for creating table , sequences or views we need privilege are of two types,

• System : Creating session , table etc are all types of system privilege
• Object : Any command or query to work on tables comes under object privilege

DCL defines two commands ,

• Grants : Gives user access privilege to database
• Revoke : Take back permission from user

To allow a user to create session

grant create session to username ;

To allow a user to username create table

grant create table to username;

To provide user with some space on table space to store table

alter user usename quota unlimited on system ;

To grants all privilege to user

grant system to username

To grant permission to create any table

grant create any table to username

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

Thanks…

Visit for

1. Stored Procedures in PostgreSQL : https://medium.com/@haripriyapawar006/stored-procedures-in-postgresql-eb451aa6ec46
2. ORDER DATABASE : https://medium.com/@haripriyapawar006/order-database-3176d1d7cd99