PostgreSQL Standard SQL Gap Analysis

Last week I’ve presented my “PostgreSQL Standard SQL Gap Analysis” at PGCon.org in Ottawa. If this sound familiar you might confuse it with the opposite talk “Features Where PostgreSQL Beats its Competitors” I gave at FOSDEM and PgConf.de this year.

The abstract of the gap analysis:

PostgreSQL supports an impressive number of standard SQL features in an outstanding quality. Yet there remain some cases where other databases exceed PostgreSQL’s capabilities in regard to standard SQL conformance.

This session presents the gaps found during an in-depth comparison of selected standard SQL features among six popular SQL databases. The selected features include, among others, window functions and common tables expressions—both of them were recently introduced to MySQL and MariaDB.

The comparison uses a set of conformance tests I use for my website modern-sql.com. These tests are based on the SQL:2016 standard and attempt to do a rather complete test of the requirements set out in the standard. This includes the correct declared type of expressions as well as the correct SQLSTATE in case of errors (teaser: nobody seems to care about SQLSTATE).

This presentation covers two aspects: (1) features not supported by PostgreSQL but by other databases; (2) features available in PostgreSQL that are less complete or conforming as in other databases.

You can download the slides here [PDF; 5MB].

Below I list the covered features with a short comment for your convenience. The slides have more information including the charts that show which databases support these features.

Features Less Complete or Conforming

Let’s start with features PostgreSQL supports, but in a less conforming or complete manner than other databases.

extract

In PostgreSQL, the extract expression returns a double value rather than an exact numeric value (e.g. numeric).

[respect|ignore] nulls for lead, lag, first_value, last_value and nth_value

PostgreSQL does not support the [respect|ignore] null modifier for these window functions.

Distinct aggregates as window functions

PostgreSQL doesn’t support distinct in aggregates when used as a window function (over): count(distinct …) over(…).

fetch [first|next] …

Fetch first is the standard clause for limit. PostgreSQL does not support percentages or the with ties modifier of fetch first.

Functional dependencies

PostgreSQL recognizes only very few of the known functional dependencies described in the standard.

Features Missing in PostgreSQL

Next, I list features that are not supported by PostgreSQL, but by at least one other major database:

Row Pattern Recognition (match_recognize)

In my presentation, I’ve made it very clear that I think this is the SQL extension of the decade. If you think window functions have changed the face of SQL, here is the next leap forward.

To learn more about this, read this free technical report from ISO: Row Pattern Recognition in SQL [ZIP+PDF; 850kB]

If you want to see even more, have a look at my slides on row pattern recognition and the articles Stew Ashton wrote about it.

Temporal and Bi-temporal Tables

This covers system and application versioning and is sometimes referred to as “time travel” or “temporal validity”. The interesting fact is that—out of the seven analyzed databases—PostgreSQL belongs to the minority that don’t support anything of this yet. This is just because MariaDB 10.3 was released the week before.

What is it? Just read the best free resource on it: Temporal features in SQL:2011 [PDF; 220kB]

Generated Columns

Again, PostgreSQL belongs to the minority of databases not supporting this. Arguably, it is not so important for PostgreSQL because PostgreSQL supports indexes on expressions natively so it doesn’t need the detour via generated columns as MySQL, MariaDB, and SQL Server do.

Combined Data Change and Retrieval

This was brought to my attention by Lukas Eder on the jOOQ blog recently. It is basically the standard variant of writable CTEs (insert, update, delete in with clauses). It is a little bit more powerful because it allows you to select either the old or the new data of an update.

Partitioned Join

Watch out: this is not about table partitioning. Instead it is about filling gaps in time series. This can be easily done with an outer join if there is only one time series. If you have multiple time series in one data set and need to fill all gaps in each of these series, partitioned join is the answer.

SELECT * 
  FROM data PARTITION BY (grp) 
 RIGHT JOIN generate_series(...) 
         ON ...

listagg

I’ve written a full article about listagg before. Sure, PostgreSQL supports string_agg and other means to get a similar result, but that’s not standard. How cares? Well, SQL Server has a string_agg function too, but with a different syntax. This is what standards aim to prevent.

Distinct data types

This is about create type ... as <predefined type>. PostgreSQL supports structured types and domains, but not this particular way introduce a new type name, including type-safety, based on a predefined type such as integer.

Work in Progress

Finally, I’ve also mentioned two topics that are currently under construction:

merge

The standard way for upsert (update or insert), featuring a more flexible syntax. This was already committed for PostgreSQL 11 but got reverted shortly after. However, chances are that there’ll be a new attempt for PostgreSQL 12.

I’ve tested the patch for syntactical completeness before it got reverted and found no major gap to the other available implementations.

JSON

PostgreSQL has great JSON support. However, in late 2016—years after PostgreSQL added it—the standard added JSON functions too. No surprise they don’t match the PostgreSQL functions. In the meanwhile other databases get standard JSON support and so does PostgreSQL.

My preliminary test of the PostgreSQL SQL/JSON patches has shown some issues, but I did not yet check them in detail. I plan to do so in the next weeks and will report any gaps I might find.

The last slide is my offer to help the PostgreSQL community in interpreting the standard and testing patches if you ping me.

Please remember that this blog post is just a teaser. More background is available in the slides [PDF; 5MB].