Advanced Features in PostgreSQL

Now just over 20 years old since its first release in January 1997, PostgreSQL has always been second in popularity to MySQL. MySQL has been the most popular thanks to it being the M in LAMP. Every Linux webhosting includes MySQL free despite PostgreSQL being the more advanced.

MySQL was always considered the faster of the two but for many years it used the non-transaction MyISAM database engine. These days the InnoDB engine which does transactions is used by default and speed isn’t now cited as a difference. PostgreSQL has always treated each SQL statement as being executed within a transaction.
In popularity terms MySQL is still way ahead of PostgreSQL though it’s only one position behind according to the db-engines ranking chart.

In this article I’ll be focussing on some of PostgreSQL’s more advanced and interesting features. Just as some programming languages make things easy to do, some others make it hard. For example string manipulation in C is time consuming to code, difficult and an error can kill the program but in C++/C#/Java it’s much easier.

PostgreSQL has been very well thought out and makes life simpler. For instance the Text field, char and VarChar in PostgreSQl are all UTF-8. There’s actually so many nice features that I can’t include everything.

Arrays

You can have an array in a PostgreSQL column. Just add [] to the definition. You can also specify the number of elements and multi-dimensional arrays.

CREATE TABLE tictactoe (
    squares   integer[3][3]
);

insert into tictactoe 
VALUES '{{1,2,3},{4,5,6},{7,8,9}}'

Storing multiple values in a row is quite handy. Arrays are supported in PostgreSQL so not only fields in tables, but parameters to functions can be arrays. You can also access rectangular subarrays (i.e. a slice of an array). More on arrays here.

Here’s a very easy way to generate an array with a select statement:

SELECT ARRAY[2012, 2013, 2014] As years;

 

Class Inheritance between Tables

Just like object-oriented classes, Tables in PostgreSQL can be inherited from other tables by adding the statement Inherits tablename. This is not the same as ANSI SQL 1999’s inheritance.

This example (from the PostgreSQL inheritance documentation) shows it in action with capitals inheriting from cities.

CREATE TABLE cities (
  name       text,
  population real,
  altitude   int     -- (in ft)
);

CREATE TABLE capitals (
  state      char(2)
) INHERITS (cities);

Here the table capitals has name, population and altitude as well a state columns. It’s important to bear in mind that as of PostgreSQL 9.6 (the current one), some things only work on the child table not the tables that inherit from it. For example indexes and foreign key constraints. There’s more about this in section 5.9.1 on this page.

Writing External Functions

Many relational databases support stored procedures but PostgreSQL lets you write user-defined function in any of these procedural languages: Python (2.* or 3.*), Perl, R, Java, Lua, sh, Tcl, Ruby, Scheme and PHP. Plus separately compiled C/C++ that’s statically linked. Note you have to install these,
You can even use Google’s V8 (the JavaScript engine inside Chrome) in the third-party project PL/b8 to let you write the user-defined functions in JavaScript.

That’s all in addition to PL/PGSQL the main language for writing stored procedures etc and is installed by default. It’s similar in some ways to MS SQL SERVER’s T-SQL but considered more powerful and useful. The example below is a function sum that adds two strings by concatenating them.

CREATE OR REPLACE FUNCTION sum (text, text)
RETURNS text AS $$
  BEGIN
    RETURN $1 || ’ ’ || $2;
  END;
$$ LANGUAGE plpgsql;

When called

SELECT sum(’hello’, ’world’);

It outputs

hello world

 

JSON and JSONB support

With NoSQL databases coming to the fore in the last six or seven years, PostgreSQL can now compete. There are two types JSON (Short for JavaScript Object Notation) a way of holding an object as a text object that conforms to JavaScript object standard. JSONB stores JSON in a binary format. JSON keeps white spaces etc. but JSONB doesn’t. It’s slightly faster for processing.
The point about NoSQL databases is because of a lack of schema, and the way data is stored they are often much faster at storing data. Games like Farmville typically need to do 10-20 databases updates a second, something that relational databases would struggle to handle at this scale.
Using JSON and JSONB, PostgreSQL can do NoSQL type operations on data held in a relational database, thus getting the best of both worlds. In NoSQL there’s no schema, it’s unstructured compared to doing things the RDBMS way. There’s a useful tutorial on getting started with JSON in PostgreSQL.

Conclusion

I’ve tried to restrict this to advanced features that are found only in PostgreSQL. For example both it and MySQL have spatial functions. I’ve used MySQL for at least 15 years on the web and never really had a use for PostgreSQL but if I needed an open source database, PostgreSQL would now be my first port of call. The more I’ve used it, the more I like it.

Post a Comment

Your email address will not be published.