Postgresql Tutorial

What is PostgreSQL?

PostgreSQL is one of the powerful and open-source data management tools. This tool is based on the object-oriented database management system. This tool helps the user to extend all kinds of SQL language that offers many advanced features that store the data safely and supports the scalability of the most complicated data. We can pronounce this PostgreSQL as (Post-gres-ql). In the year 1986 at the University of California, Berkeley developed this PostgreSQL project. It has more than 30 years of an active database development used on many-core platforms. PostgreSQL supports almost all kinds of relational database management features that are not present in other query language tools such as SQL and MySQL.

Take your career to next level in PostgreSQL with HKR. Join PostgreSQL online training now

History of PostgreSQL

PostgreSQL was created by the computer science professor at the University of California named Michael Stonebraker. The original name of this tool was Postgres.

1986-> It was a Postgre project at university of California, Berkeley

• Michael Stonebraker project
• The successor to the development of INGRES

1994-> First commercialized as an Illustra

1995-> open-source development started

• After that, they ported to SQL tool
• Then PostgreSQL global development group formed

1997-> ported to different languages like Japanese, they developed to support in Japan

1999-> First-time full-time developer and corporate support

2004-> Native window support

2006-> Supported by Sun.

Versions of PostgreSQL

Let us know the different versions of PostgreSQL:

1996-> PostgreSQL 6.0

1997-> PostgreSQL 6.1 and PostgreSQL 6.2

1998-> PostgreSQL 6.3 and PostgreSQL 6.4

1999-> PostgreSQL 6.5

2000-> PostgreSQL 7.0

2001 -> PostgreSQL 7.1

2002-> PostgreSQL 7.2 and PostgreSQL 7.3

2003-> PostgreSQL 7.4

2005 and 2006-> PostgreSQL 8.0

Currently we are using PostgreSQL 9.4.15

PostgreSQL Training

• Master Your Craft
• Lifetime LMS & Faculty Access
• 24/7 online expert support
• Real-world & Project Based Learning

Explore Curriculum

Features of PostgreSQL

The major features of PostgreSQL are:

1) Portable:

• Written in ANSI C language
• Supports major operating systems Windows OS and Linux OS, Mac operating system and UNIX OS platforms.

2) Reliability:

• Fully Acid Compliant
• Supports data transactions
• Supports the data types and provides Savepoints
• Provides the Write-Ahead Logging (WAL).

3) Scalable:

• Uses Multi-versions
• Concurrency Control
• Supports Table Partitioning
• Supports Tablespaces
• Supports Parallel Sequential Scans

These are the major features of PostgreSQL.

Why is PostgreSQL?

This is an important question that comes to mind when you begin your journey as a PostgreSQL; Let me give a brief idea about why it is PostgreSQL.

• PostgreSQL is an open-source and object-oriented relational database management support tool.
• PostgreSQL is a community-driven platform as it consists of the core team, committers, contributors and developers
• It’s a free tool no one company owns it
• It offers great mailing list support
• Available yearly new release upgraded versions
• Great performance-oriented and replications in the last few years
• License is more flexible and non-expensive than GPL
• The non-technical team can also work on this platform.
• These are all the most available features of PostgreSQL.

What are the advantages and disadvantages of PostgreSQL?

Till now we have discussed PostgreSQL definition, its history, and main features. Now I am going to discuss its advantages and disadvantages. Firstly know the advantages of PostgreSQL,

Advantages of PostgreSQL:

PostgreSQL is an open-source SQL standard compliant RDBMS tool. It supports all relational data types, views, and features of RDBMS.

• We can work on complicated data types.
• This tool supports users to create their data types or customized data creations.
• Strong community support to ask any queries related to the tool.
• Strong third-party support.
• Extensible and object-oriented tool.

Disadvantages of PostgreSQL:

• Performance is not up to the mark while working on a complex platform.
• Less popularity compare to MySQL
• Hosting is poor.

These are the advantages and disadvantages of the PostgreSQL tool.

Subscribe to our youtube channel to get new updates..!

Subscribe

PostgreSQL Data Types

PostgreSQL supports rich data types. Users can create their data types. This PostgreSQL offers customized data type creations. With the help of the CREATE TYPE command, we can create new data types. 

The following data types are available in PostgreSQL tool:

• Text data types
• Numeric data types
• Dates/Times data types
• XML data type
• JSON data type
• Boolean data type
• Bits data type
• Binary data type
• Network data type
• Arrays data type
• Boolean expression data type
• Temporal data types
• UUID data types
• Array data types
• JSON data types
• Data types for storing a network and geometric data.
• Data tables
• Data filters
• Data queries

Let us discuss one by one,

Character Data types:

Character Data types in PostgreSQL support storing text values. These character data types help to create the same internal structures. There are 3 types of character data types available:

CHAR (n) -> in this we can add fixed-length and blank characters

VARCHAR (n) -> in this type we can create variable-length with a limit

Text-> in this type we can create data types with unlimited data lengths.

Numeric data types:

PostgreSQL supports two types of numeric data types.

Integers -> to store integer data types

Floating-point -> to store floating-point types.

Binary data types:

PostgreSQL supports storing binary data types; they can be octets or bytes. There are two types of binary data types available such as,

• Binary data types help you to store odds of zero values
• Storing non-printable octets.

Whereas character string does not allow storing of zero octets and disallows any other sequence of octet values.

Network data types:

Many network applications in PostgreSQL allow users to store network information such as IP addresses and sensors.

There are three types of network data types available those are,

• Cider
• Inet
• Macaddr

There are lots of advantages to using Network data types:

• Saving storage space
• Error checking especially input errors
• Searching data by subnet

Text search data types:

This PostgreSQL supports two types of data which are mainly designed to create or support searching of the full text. Full text is a collection of natural-language documents.

There are two types of text search data types available such as,

• Tsvector-> this data type represents documentations which are in a form optimized for searching text.
• Query data type text stores the keywords that are needed to be searched.

Date/Time Data types:

The date/ time data type helps the user to store the time zones and date. By using these data type

You can also change the date/time formats including traditional PostgreSQL format, ISO

8601 and SQL compatible etc.

There are six types of Date/Time data types available:

• Timestamp without time zone
• Timestamp with time zone
• Date
• Time without time zone
• Time with time zone
• Interval

Boolean Data types:

There are three types of Boolean data types available,

• True
• False
• Null

Geometric data types:

This Geometric PostgreSQL data type supports two-dimensional spatial objects. By using this data type we can perform operations like rotations, scaling and translation, etc.

There are 7 types of Geometric data type available,

• Point
• Line
• Lseg
• Box
• Path
• Polygon
• Circle

Enumerated data types:

Enumerated data types are useful for the creation of rarely changing information such as Country code or branch id. This data type helps create a table with foreign keys to ensuring data integrity.

Range Data type:

This Range data type is used mainly for creating many business applications that require data in ranges.

• Int4range
• Int8range
• Numrange
• Tstrange
• Strange
• Date range

UUID type:

Universally Unique Identifier (UUID) is of the 128-bit quantity which is used in the generation of an algorithm. This Identifier is mainly used for the distributed systems because it provides uniqueness within a single database. UUID is mainly written as a group of lower-case hexadecimal digits and here we use hyphens to separate the groups.

XML type:

This XML data type in PostgreSQL allows users to store XML data in data types, in the form of extensions. It adds extensions to text data types.

JSON data type:

This JSON PostgreSQL offers 2 data types such as,

JSON-> a simple extension of text data types with JSON validation

JSONB-> A binary representation of the JSON data.

Pseudo data types:

PostgreSQL has many special entries that are called Pseudo-types. But we can't use them as a column-data type. This type is used to declare or function's arguments or return type.

Data tables:

This data type of PostgreSQL is used in the creation of data tables. For example,

• Create
• Delete
• Insert
• Drop
• Alter
• Rename
• Copy

Data Filters:

The key benefit of using this data filter is more readable than the when case statement. Let us get into the brief detail,

• Using SQL keyword with the FILTER clause in PostgreSQL
• In this case, we use Filter in a case such that,
• AS
• ANY and ALL
• FROM
• Using the COUNT () -> this Function is used to filter the number of data records when it matches the query.
• One more function we can use here that is, “CASE WHEN” this is the replacement to PostgreSQL FILTER TYPE.

Data Queries:

This type is used to retrieve the data from the database. In the PostgreSQL query data, type uses the SELECT command to specify the queries.

• SELECT select-list from table…..(insert, delete, create and drop)
• SELECT * FROM table…( used to query the multiple tables)
• Combining queries: this data type is used to combine two or more queries results using operations like UNION, INTERSECTIONS, and DIFFERENCE.

Query1 UNION [ALL] query2

Query1 INTERSECT [ALL] query2

Query1 EXCEPT [ALL] query2

PostgreSQL Functions

PostgreSQL function, also known as stored procedures, allows users to perform many operations and queries within the database. This function in PostgreSQL allows the database to reuse other applications.

The basic syntax of this function is as follows,

CREATE [OR REPLACE] FUNCTION function_name (arguments)

 …………..

Function totalRecords () syntax is as follows,

 CREATE OR REPLACE FUNCTION total records ()

……………

Few of important functions we use those are,

PostgreSQL COUNT Function

PostgreSQL MAX Function

PostgreSQL MIN Function

PostgreSQL AVG Function

PostgreSQL SUM Function

PostgreSQL ARRAY Function

PostgreSQL NUMERIC Function

PostgreSQL STRING Function

PostgreSQL Applications:

As I said earlier this PostgreSQL database tool is widely used in many industries. Let us discuss the application of PostgreSQL:

Financial Industry:

PostgreSQL database is an ideal tool for financial industries, as it is completely ACID-compliant which supports the creation of OLTP (online transaction processing). It allows users to work on database analytics. This PostgreSQL also offers integration with much software like MATLAB and R.

Government GIS data:

PostgreSQL database tool offers the most powerful GIS applications they are called "PostGIS". This GIS extension supports thousands of functions to work with geometric data in different available formats.  In recent times, PostGIS is highly standard compliant.

In PostgreSQL, we can see two GIS tools such as QGIS and Geoserver which offer the easiest methods to handle the Geodata.

Manufacturing industry :

Nowadays many manufacturing industries have started using the PostgreSQL database tool, as it offers a feature that helps to speed up their business applications. This PostgreSQL optimizes supply chain management performance. One of the main advantages of using this database tool is that, helps to reduce the business operation cost.

If you have any doubts on PostgreSQL, then get them clarified from PostgreSQL Industry experts on our PostgreSQL Community!

Web development technologies:

Most of the web development companies prefer PostgreSQL to increase their scalability as they deal with thousands of requests per second at a time. To handle these requests, the PostgreSQL tool proves the best solution. Many features of PostgreSQL offer the best work experience with modern web frameworks like Node.js, ext.js, and Django.

Scientific data:

When you are working on research and science-related project, it needs to generate terabytes of data. It is very important to handle the data in an effective way. Data analytics and an effective engine help you to manage a large amount of data with no trouble.

Difference between PostgreSQL and MySQL

Now it’s time to know the difference between PostgreSQL and MySQL

How to download and install PostgreSQL?

Step by step procedure to how to download and install the PostgreSQL tool,

Step 1: First go to the website PostgreSQL Download and select window as shown in the below figure,

Step 2: two options available,

• Interactive installer by EnterpriseDB
• Graphic installer by BigSQL

But BigSQL currently installs pgAdmin version 3 which is deprecated. So it’s better to choose Enterprise DB which installs the latest version 4

Step3: Now you will be taken into installing your desired PostgreSQL version and OS. Choose the latest version and OS as per your environment-> click the Download button

The download will start like this,

Step4: Open the Exe file-> click the next on the install screen

Step 5: Change the installation directory, or if you don't want to leave it to default-> click next

Step 6: Choose the component you want to install, you can uncheck stack builder-> click next

Step7: you may change the location of data->click next

Step8: Enter user password -> click next

Step9: Leave the port number default-> click next

Step 10: Check the pre-installation summary-> click next

Step 11: Click the next button

Step 12: Once the installation is complete, you will see the stack builder,

Uncheck that option -> click finish

Step 13: To launch the PostgreSQL -> got o start menu -> search PgAdmin4

Step 14: Now you get into the PgAdmin homepage

Step 15: Click on the servers-> PostgreSQL 10 in the left tree

Step 16: Enter user password which is set during the installation-> click OK

Step 17: Now you will see the Dashboard

Then it will take into the PostgreSQL installation.

Conclusion :

PostgreSQL is a freely usable software and open-source database management tool to support many Relational database management features. This tool offers easy access to complicated data types, as it offers many powerful tools. It helps us to create our data type and its fully customized tool. And a very important thing is that no-cost is required for purchasing this software. I hope this article may help a few of you who want to pursue their career as a database developer.