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

On my Own Behalf I make my living from training, other SQL related services and selling my book. Learn more at https://winand.at/.

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:

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