June 20, 2018 0 Comments

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:

  • 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

Feature PostgreSQL Microsoft SQL Server
Licensing Open Source Commercial – Closed Source, Various levels of features based on version, Free Crippleware
Server operating systems FreeBSD
HP-UX
Linux
NetBSD
OpenBSD
OS X
Solaris
Unix
Windows
Linux
Windows
Functional Indexes – indexes based on a function Yes No – but you can create a computed column and create an index on it
Partial Indexes – e.g. you want to create a unique index but only consider non-null values Yes No – but as pointed out you can achieve similar results with an indexed view
Dynamic and action SQL in functions Yes! – you can do really cool things with action functions in SELECT statements No – but you can in Stored procedures but you can’t call stored procs from SELECT statements so much more limiting than PostgreSQL
DISTINCT ON Yes No
Windowing Functions OVER..PARTITION BY No Yes
GROUP BY clause permits a WITH ROLLUP modifier No Yes
Can use “LIKE” statements with case sensitive Yes No
Subquery performance Slow 3 times faster than 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.

Install PostgreSQL step by step

1. 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.

PostgreSQL

2. Start Installing PostgreSQL. Click on Next

PostgreSQL

3. Specify installation folder – choose your own or keep the default folder suggested by PostgreSQL installer.

4. Enter the password for the database superuser and service account.

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

PostgreSQL

6. Choose the default locale used by the database.

PostgreSQL

You’ve completed providing information for the PostgreSQL installer.

7. Click the Next button to install PostgreSQL

PostgreSQL

The installation may take few minutes to complete.

PostgreSQL
 

8. 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.

PostgreSQL

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

PostgreSQL

 

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:

1
2
CREATE DOMAIN contact_name AS
VARCHAR NOT NULL CHECK (value !~ '\s');

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

1
2
3
4
5
6
CREATE TABLE mail_list (
id serial PRIMARY KEY,
first_name contact_name,
last_name contact_name,
email VARCHAR NOT NULL
);

Leave a Reply:

Your email address will not be published. Required fields are marked *