PostgreSQL 15’s new SQL MERGE command makes migrating from Oracle and SQL Server easier.
PostgreSQL, also known as “Postgres,” is an open-source object-relational database management system (ORDBMS). It aims to provide a very robust and feature-complete SQL-compatible storage from the beginning.
As a result, PostgreSQL advertises itself as “the most advanced open-source relational database in the world.” And the recently released PostgreSQL 15 version confirms this. So, let’s see what it brings us.
What’s New in PostgreSQL 15
The MERGE command, which has been used in Oracle and SQL Server for some time, now finds a home for the first time in this version of PostgreSQL.
It is an SQL statement that can handle INSERT, UPDATE, and DELETE commands in a single query, reducing the need to rewrite MERGE statements completely.
But why is this so significant? The answer is simple – it makes it easy for developers and DBAs to migrate their SQL code from Oracle or Microsoft SQL Server to PostgreSQL. As a result, PostgreSQL’s position as the most enterprise-ready free, open-source database is strengthened even more.
Merge is part of the SQL standard supported in both Oracle and SQL Server. But in Postgres, what you had to do was write multiple statements if you wanted a selective action for your code to take. The MERGE statement is helping users to be able to migrate relatively easily from Oracle to Postgres, which is an ever-popular use case.Umair Shahid, Head of PostgreSQL, Percona
Another significant new feature in PostgreSQL 15 is advanced logical replication capabilities. For those not in the know, logical replication is a technique for duplicating data objects and changes based on their replication identity, such as the primary key.
Now, the PostgreSQL 15 logical replication features the long-awaited row and column-level filters. In other words, this allows you to selectively duplicate data from your primary database to your second database.
Another noteworthy feature involves the use of log formats. Previously, PostgreSQL supported several methods for logging server messages, such as stderr, csvlog, and syslog. PostgreSQL 15 introduces the option to format your database logs in JSON.
Due to the versatility of the JSON format, the support for structured server log output can also be used for storage and analysis by other services.
This popular open-source database improves its speed with each major release, and PostgreSQL 15 is no exception. For example, improvements to PostgreSQL 15’s on-disk and in-memory sorting algorithms have resulted in massive speedups.
Of course, PostgreSQL 15 includes many other interesting new features and improvements. The official announcement or release notes contain a complete list of them.