Advanced PostgreSQL Features: A Guide

Introduction

Despite the rise in popularity of NoSQL databases, relational databases still continue to be preferred for many applications. This is because of their strong querying abilities and their robustness.

Relational databases excel in running complex queries and reporting based on the data in cases where the data structure does not change frequently. Open-source relational databases like MySQL and PostgreSQL provide a cost-effective alternative as a stable production-grade database compared to licensed counterparts like Oracle, MSSQL, etc. If your organization works extensively with such a relational database, you should check Arctype - A collaborative SQL editor that helps visualize a database like a spreadsheet. This post is about the advanced features of PostgreSQL - A free and open-source database management system that focuses on extensibility and SQL compliance.

PostgreSQL - Common Use Cases

PostgreSQLs unique feature-set allows it to be used as a transactional database or as a data warehouse. The excellent transaction support and high write performance make it an ideal candidate for running online transactional processing workloads. At the same time, the rich built-in functions that include analytical and windows functions allow it to be used as a data warehouse that can be used by the analysts to derive insights.

JSON data types and JSON functions in PostgreSQL make it possible to run NoSQL workloads using PostgreSQL. Another unique feature that PostgreSQL supports is the ability to store geospatial data and run queries over them. This is made possible by the GIS plugin. The full-text feature in PostgreSQL helps one to search documents without using complex regular expressions. Other than this, PostgreSQL also supports many advanced features like schema inheritance, nonatomic columns, views, etc. We will take a detailed look into these advanced features in the next section.

PostgreSQL - Advanced features

Inheritance

PostgreSQL supports inheritance to enable users to create clean tables that model their model structures. For example, imagine you have a use-case where there is a customer table and there is a specific type of customer with an additional field called office_address. In a database that does not support inheritance, this has to be handled through two separate tables using the below DDL statements.

CREATE TABLE CUSTOMERS (    name text,    age int,    address text);

CREATE TABLE OFFICE_CUSTOMERS(    name text,    age int,    address text,    office_address text);

With PostgreSQLs inheritance feature, this can be handled using the below DDL statements. Using Arctype, create DDL statements as below.

CREATE TABLE CUSTOMERS (    name text,    age int,    address text);CREATE TABLE OFFICE_CUSTOMERS(    office_address text) INHERITS (customer)

The above query will create two tables with the office_customers table inheriting from the main customer table.

This helps in keeping the schemas clean as well as in database performance.

Let us now insert a few entries to see how this works.

INSERT INTO CUSTOMERS VALUES('ravi','32','32, head street');INSERT INTO CUSTOMERS VALUES('michael','35','56, gotham street');INSERT INTO OFFICE_CUSTOMERS VALUES('bane','28','56, circadia street','92 homebush');

If you need to access data from only the first table, you can use the ONLY keyword. This is done as below.

SELECT * from ONLY CUSTOMER WHERE age > 20 ;

The result will be as follows.

If you need to retrieve the entries from both tables, you can use the query without the ONLY keyword.

SELECT * from CUSTOMERS WHERE age > 20 ;

The result will be as follows.

Non-Atomic Columns

One of the main constraints of a relational model is that columns should be atomic. PostgreSQL does not have this constraint and allows columns to have sub-values that can be accessed via queries.

It is possible to create tables with fields as arrays of any data type. Open Arctype and try creating a table using the below statement.

CREATE TABLE customer (   name            text,   Address         text,   payment_schedule  integer[],   );

For the above table, the payment_schedule field is an array of integers. Each integer can be accessed separately by specifying the index number.

Insert a few rows via the below command.

INSERT INTO CUSTOMER_SCHEDULE VALUES( 'jack','Athens, Colarado','{1,2,3,4}')INSERT INTO CUSTOMER_SCHEDULE VALUES( 'jackson','Tennessey, greece','{1,7,3,4}')

The result will be as follows.

SELECT * FROM CUSTOMER_SCHEDULE WHERECUSTOMER_SCHEDULE.payment_schedule[1] <> CUSTOMER_SCHEDULE.payment_schedule[2];

The result will be as follows.

Window functions

PostgreSQL window functions play an important role in making them a favorite for analytics applications. Window functions help users to execute functions spanning over multiple rows and return the same number of rows. Window functions are different from the aggregate functions in the sense that the latter can only return a single row after aggregation.

Imagine you have a table with employee name, employee id, salary, and division name.

CREATE TABLE employees (   empno            int,   salary         float,   division  text   );

Now, let us insert some values into the table.

INSERT INTO employees VALUES(1,2456.7,'A')INSERT INTO employees VALUES(2,10000.0,'A');INSERT INTO employees VALUES(3,12000.0,'A');INSERT INTO employees VALUES(4,2456.7,'B');INSERT INTO employees VALUES(5,10000.0,'B');INSERT INTO employees VALUES(6,10000.0,'C');INSERT INTO employees VALUES(7,2456.7,'C');

Now suppose you want to display the average salary of a division along with the employee details. You can achieve this using the below query.

SELECT empno, salary, division,avg(salary) OVER (PARTITION BY division) FROM EMPLOYEES;

The output will be as below.

Support for JSON Data

The ability to store and query JSON allows PostgreSQL to run NoSQL workloads as well. Suppose, you are designing a database to store data from various sensors and you are not sure about the specific columns that you will need to support all kinds of sensors. In that case, you can design a table such that one of the columns is JSON to store the unstructured or frequently changing data.

CREATE TABLE sensor_data (    id serial NOT NULL PRIMARY KEY,    data JSON NOT NULL);

You can then insert data using the below statement.

INSERT INTO sensor_data (data)VALUES('{ "ip": "J10.3.2.4", "payload": {"temp": "33.5","brightness": "73"}}');

PostgreSQL allows you to query the specific fields in the JSON data via the below construct.

SELECT data->> 'ip' as ip from sensor_data;

Full-text Search

The full-text search feature in PostgreSQL allows one to search for documents based on the words that are present in the field. You might say that any database that supports a LIKE query construct should be able to do it. But PostgreSQLs full-text search goes one step above what LIKE provides. To do a complete search using the LIKE query for a word, you will need to use elaborate regex expressions. On the other hand, PostgreSQL's full-text feature will even allow searching based on the root words or lexeme. A lexeme is best explained using an example. Lets say you want to search your document for the word work in a document. The word work may be present in the document in multiple forms like Working, works, worked etc. PostgreSQL full-text search is intelligent enough to consider all such forms in the search. Imagine the trouble if you try to do this using regular expressions and LIKE queries. Let us see the full-text search in action now.

Let us create a table LOG for storing error logs.

CREATE TABLE log(   name text,   description text);

Now, insert a few error values using the below statements.

INSERT INTO LOG VALUES('ERROR1','Failed to retreive credentials');INSERT INTO LOG VALUES('ERROR2','Fatal error. No records present. Please try again with a different value');INSERT INTO LOG VALUES('ERROR3','Unable to connect. Credentials missing');

Let us now try to retrieve the rows that contain the word miss using the full-text search. This needs two functions. The to_tsvector function converts the values to their lexeme and the to_tsquery function will try to match the words.

SELECT * FROM LOG WHERE to_tsvector(description) @@ to_tsquery('miss');

The result will be as follows.

Note that the query managed to return the row even though the actual word in the description was missing and not miss that we searched for.

The full-text features are available with various localization configurations. Hence it can be used with many languages too.

Postgres Views

Views can be considered as virtual tables that meet specific criteria. For example, lets assume you have a staff table and salary table. Where id is the common key. If your workflow requires you to combine these tables often and analyze them, you can define a view with your desired criteria. This view will reflect all changes in the underlying tables and will act like a combined table. Lets see this in action below.

Create the tables using the below DDL statements.

CREATE TABLE staff(   id int,   age int,   address text);CREATE TABLE salary(   sal_id int,   salary int,   division text);

Insert a few entries using the below statements.

INSERT INTO STAFF VALUES(1,32,'michael');SELECT * FROM STAFF_SALARY_COMBINED;INSERT INTO STAFF VALUES(2,32,'sarah');INSERT INTO SALARY VALUES(1,18000,'A');INSERT INTO SALARY VALUES(2,32000,'B');

Create a view using the below statement.

CREATE VIEW STAFF_SALARY_COMBINED AS (    SELECT *     FROM staff,salary     WHERE id=sal_id);

You can then query the view as a normal table.

SELECT * FROM STAFF_SALARY_COMBINED;

The output in Arctype will be as follows.

Handling Geospatial Data in Postgres

PostgreSQL with GIS extension allows one to store geometrical coordinates and shape information in tables. What makes it special is the ability to execute queries over these coordinates through built-in functions to calculate distances, area, etc. This makes PostgreSQL the de facto choice in use cases where the output or inputs are geographical coordinates and there are significant spatial processing requirements. For example, let's say you have the geometric information of all the cities and you want to know the area of a city, it is as easy as executing the below query.

SELECT city.nameFROM burn_area, cityWHERE ST_Contains(burn_area.geom, city.geom)

The result will display the city name meeting the coordinate conditions provided.

Conclusion

PostgreSQL has many advanced features that make it unique in the sea of relational databases. Taking advantage of these unique features and collaborating with others is the foundation of building powerful applications using PostgreSQL. Having access to an SQL editor that can help you take advantage of these advanced features can make your job easier. Check out Arctype - A collaborative SQL editor that helps you build dashboards and visualize tables like spreadsheets.