PostgreSQL Basics

PostgreSQL is an ORDBMS for managing data stored in relations. It offers many features such as complex queries, triggers, transactional integrity, multiversion concurrency control.
Also, PostgreSQL can be extended by the user in many ways, data types, functions,aggregate functions, index methods.

PostgreSQL uses a client/server model.

server : postgres is a database server program, running as a process to perform these actions.,

• manages the database files.
• accept database connections from the client applications.
• performs database actions on behalf of the clients.

client : front end application wants to perform database operations, client applications could be

• text-oriented tool
• database maintenance tool
• a web server that accesses the database to display web pages.

The PostgreSQL server can handle multiple concurrent connections from clients.

Download & Install the PostgreSQL for windows OS, click here.

After completing the installation, open the client SQL Shell(psql) for executing the queries.  Following  snapshots will guide you.

Provide the correct authentication details to login psql

Basic PSQL commands

Learn these basic psql slash commands which will aid you to use SQL Shell easily. Also these commands are completely different from MySQL.

Command	Actions
\l	List all databases
\c dbname	Connect to new database.
\dt	To view list of relations/tables
\d tablename	Describe the details of given table.
\h	Get a help on syntax of SQL commands
\?	Lists all psql slash commands.
\set	System variables list.
\q	Quit psql

Basic SQL operations in PostgreSQL

Database Creation & Deletion
To create a new database

createdb dbname

Remove the database

dropdb dbname

CRUD – Create Read Update Delete

These are the four basic functions to be performed on relational databases. Almost Postgres CRUD  SQL statements are similar to MySQL statements.

Create – Creating table & inserting the values.
Table Creation : Books

CREATE TABLE Books(BookId int, BookName text, BookCost int);

Table Values Insertion

INSERT INTO Books VALUES(1001, 'Who will cry when you die', 299);
INSERT INTO Books VALUES(1002, 'Practical PostgreSQL', 1722);
INSERT INTO Books VALUES(1003, 'Beginning Databases with PostgreSQL', 1080);

Read – Retrieve table data.

SELECT * FROM Books;

bookid |              bookname               | bookcost
——–+————————————-+———-
1001 | Who will cry when you die           |      299
1002 | Practical PostgreSQL                |     1722
1003 | Beginning Databases with PostgreSQL |     1080
(4 rows)

Update

UPDATE Books SET BookCost=1100 WHERE BookId=1003;

SELECT * FROM Books WHERE BookId=1003;

bookid |              bookname               | bookcost
——–+————————————-+———-
1003 | Beginning Databases with PostgreSQL |     1100
(4 rows)

Delete – Delete row(s) from the relation.

DELETE FROM Books WHERE BookId=1001;

SELECT * FROM Books;

bookid |              bookname               | bookcost
——–+————————————-+———-
1002 | Practical PostgreSQL                |     1722
1003 | Beginning Databases with PostgreSQL |     1100
(3 rows)

Other Significant Features

Inheritance
PostgreSQL allows you to implements table inheritance. This should be defined with a special keyword INHERITS in the table design.

CREATE TABLE BookAuthors(Author1 text, Author2 text) INHERITS (Books);

Table inheritance has certain limits in inserting data into table. As Postgres won’t allow to perform such operations. Lets illustrates the possible fail & success cases.

case 1: You can’t insert directly into BookAuthors table.

INSERT INTO BookAuthors VALUES('Richard','Matthew');

ERROR:  invalid input syntax for integer: “Richard”
LINE 1: INSERT INTO BookAuthors VALUES(‘Richard’,’Matthew’);

case 2: It does not allow to insert into child table columns (Author1, Author2) while inserting data into parent table.

INSERT INTO Books VALUES(1006, 'Greatness Guide', 400, 'Robin','Sharm
a');

ERROR:  INSERT has more expressions than target columns
LINE 1: …T INTO Books VALUES(1006, ‘Greatness Guide’, 400, ‘Robin’,’S…
^

Possible valid insert statements are

case 1: This will insert data into Books table alone.

INSERT INTO Books VALUES(1006, 'Greatness Guide', 400);

case 2: This will insert data into both parent & child tables, Books & BookAuthors.

INSERT INTO BookAuthors VALUES(1005, 'PostgreSQL 9.0 High Performance
', 1500, 'Gregory Smith');

Retrieving the child table BookAuthors will also retrieve the parent table column values.

select * from BookAuthors;

bookid |            bookname             | bookcost |    author1    | author2
——–+———————————+———-+—————+———
1005 | PostgreSQL 9.0 High Performance |     1500 | Gregory Smith |
(1 row)

ONLY Keyword
This keyword indicates that the query should be run only on the specified table(Books) and not tables in the inheritance hierarchy. ie., Insertion done through child table will not be shown here.

Note : ONLY Keyword will be applicable for SELECT, UPDATE & DELETE.

SELECT * FROM ONLY Books;

bookid |              bookname               | bookcost
——–+————————————-+———-
1002 | Practical PostgreSQL                |     1722
1004 | Beginning Databases with PostgreSQL |     1100
(2 rows)

Row(BookId : 1005) is not shown here because this has been inserted through child table BookAuthors.

Composite Types
This represents the structure of a row or record. PostgreSQL allows composite types to be used as the same ways of simple types are used. For instance, a column of a table can be declared as a composite type. See the below example.

Creating two composite types FullName & Address, each has 2 & 3 columns respectively.

 CREATE TYPE FullName AS(FirstName text, LastName text);
 

 CREATE TYPE Address AS(DoorNo text, StreetName text, Place text);
 

\dT command can be used to see the defined composite types.

List of data types
Schema |   Name   | Description
——–+———-+————-
public | address  |
public | fullname |
(2 rows)

Defined composite types FullName & Address being used in the Employee table creation.

CREATE TABLE Employee(EmpId int PRIMARY KEY, EmpName FullName, EmpAddress Address);

Inserting data into the composite column values can be done in two ways.
ROW – expression used to construct composite values
() – Simply include the composite values in the bracket.

 INSERT INTO Employee VALUES(100, ROW('Neil','Matthew'), ROW('No. 6','Vivekanandar Street','Dubai'));
 

Inserted data.

empid |    empname     |              empaddress
——-+—————-+—————————————
100 | (Neil,Matthew) | (“No. 6″,”Vivekanandar Street”,Dubai)
(1 row)

Want to access a field of a composite column, use this notation. (composite_type_name).fieldname. For multi-table queries you can use table name as well, like this (table_name.composite_type_name).fieldname

 select empid, (empname).firstname, (empaddress).place from Employee;
 

empid | firstname | place
——-+———–+——-
100 | Neil      | Dubai
(1 row)

ORDBMS

Object Relational Database Management System(ORDBMS) is a database management system acts as a bridge between objects & relational databases.

The main objective of ORDBMS is to achieve the benefits of both the relational and the object models such as scalability, support for oo features & custom types.

It directly supports object oriented paradigms objects, classes, inheritances , polymorphism in the database schemas & query language. Also has the ability to extensible with user-defined types & methods.

Advantages

• Supports rich data types such as audio, video and image files.
• Access and manipulate the complex data types in the database without breaking the rule of relational model.

Disadvantages

• Complexity

ORDBMS vendors in the market : Oracle, SQL Server, PostgreSQL, Informix. Among these, PostgreSQL is an Open Source ORDBMS.