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.
- 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
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.
2. Start Installing PostgreSQL. Click on Next
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.
6. Choose the default locale used by the database.
You’ve completed providing information for the PostgreSQL installer.
7. Click the Next button to install PostgreSQL
The installation may take few minutes to complete.
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.
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]