You may be asking yourself “Why PostgreSQL?” There are several choices for open source relational databases out there (we looked at MySQL, MariaDB and Firebird), but what does PostgreSQL have that they don’t? PostgreSQL’s tag line claims that it’s: “The world’s most advanced open source database.” We’ll give you a few reasons why PostgreSQL claims this.

What is PostgreSQL?

PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions.

Why use PostgreSQL?

In addition to being free and open source, PostgreSQL is highly extensible. For example, you can define your own data types, build out custom functions, even write code from different programming languages without recompiling your database.

It supports a large part of the SQL standard and offers many modern features:


.blog-ul-bull li{list-style: inside disc;}
.post-table{border:1px solid #e3e2e2;font-size:14px;}
.post-table thead{background:#eee;}
.post-table tbody{border:1px solid #e3e2e2;}
.post-table td{padding:7px 10px;}
.post-table tr:nth-child(even) {background: #eee}
.post-table tr:nth-child(odd) {background: #FFF}


• Complex queries
• Foreign keys
• Triggers
• Updatable views
• Transactional integrity
• Multiversion concurrency control
• Data Integrity

Also, PostgreSQL can be extended by the user in many ways, for example by adding new

• Data types
• Functions
• Operators
• Aggregate functions
• Index methods
• Procedural languages

Below is an inexhaustive of various features found in PostgreSQL :

Data Types:

• Structured: Date/Time, Array, Range, UUID
• Document: JSON/JSONB, XML, Key-value (Hstore)
• Geometry: Point, Line, Circle, Polygon
• Customization: Composite, Custom Types

Key differences between PostgreSQL vs MS SQL

Install PostgreSQL

In this, we will show you how to install PostgreSQL on your local system for learning and practicing PostgreSQL.

Download PostgreSQL Installation Setup for Windows

You need to download the installation setup from PostgreSQL Official website.

• Go to the PostgreSQL official website, download section for Windows http://www.postgresql.org/download/windows/
• Click on the download setup from EnterpriseDB
• Choose the latest version to download. It takes few minutes to complete the download.

Install PostgreSQL step by step

• Double click on the installer file, an installation wizard will appear and will guide you through multiple steps where you can choose different options that you would like to have in PostgreSQL.

  

• Start Installing PostgreSQL. Click on Next

  

• Specify installation folder – choose your own or keep the default folder suggested by PostgreSQL installer.
• Enter the password for the database superuser and service account.

  

• Enter the port for PostgreSQL. Make sure that no other applications are using this port. Leave it as default if you are unsure.

  

• Choose the default locale used by the database.

  

  You’ve completed providing information for the PostgreSQL installer.

• Click the Next button to install PostgreSQL

  

  The installation may take few minutes to complete.

  

• Click the Finish button to complete the PostgreSQL installation.

Verify the Installation

There are several ways to verify the installation. You can try to connect to the PostgreSQL database server from any client application e.g., psql and pgAdmin.

The quick way to verify the installation is through the pgAdmin application.

Click on pgAdmin to launch it. The pgAdmin GUI will be displayed as below.

A Look at PostgreSQL User-defined Data Types

Besides the built-in data types, PostgreSQL allows you to create user-defined data types through the following statements:

CREATE DOMAIN creates a user-defined data type with constraints such as NOT NULL, CHECK, etc.

To make it easier, Create a contact_name domain as below:

[cc lang="C#"]CREATE DOMAIN contact_name AS
VARCHAR NOT NULL CHECK (value !~ '\s');[/cc]

And use the contact_name as the data type of the first_name and last_name columns:

[cc lang="C#"]CREATE TABLE mail_list (
id serial PRIMARY KEY,
first_name contact_name,
last_name contact_name,
email VARCHAR NOT NULL
);[/cc]