PostgreSQL, the popular open-source relational database, gains support for MERGE statements, a move intended to ease migration from SQL Server and Oracle-based systems.
With the release candidate for PostgreSQL 15 released on Friday and expected general availability on October 13, the upgrade promises a host of new features for developers.
But for those eyeing an Oracle to PostgreSQL migration, the new MERGE implementation may be the focus of attention.
MERGE allows the developer and DBAs to either insert, update, or delete a row in the table according to certain conditions.
Umair Shahid, head of PostgreSQL at open-source consulting and software company Percona, said, “Merge is part of the SQL standard, which is supported by both Oracle and SQL Server. But in Postgres, if you wanted selective action for your code to take, you had to write multiple directives.”
Open Source Databases: What Are They and Why Are They Important?
PostgreSQL’s support for MERGE not only means fewer lines of code for the same operations, but also makes it easier to migrate from Oracle and SQL Server.
“The MERGE statement helps users migrate from Oracle to Postgres with relative ease, which is a popular use case,” Shahid said.
He argued that the SQL code already written in Oracle and SQL Server can be ported to PostgreSQL since all three databases are ANSI compliant.
“The code usually looks pretty similar. Most of the code can only work as long as the functionality is present in the database. [Before PostgreSQL 15] Otherwise, the developers who were already using the MERGE functionality in Oracle would have had to rewrite this code after switching to PostgreSQL. With this feature, you don’t have to do that. The existing code in Oracle will just work,” Shahid said.
Postgres was first proposed in 1986 by Michael Stonebraker and Lawrence Rowe of UC Berkeley [PDF] as the successor to Ingres and is currently available under the PostgreSQL license, an open source license similar to the BSD or MIT license.
Although a database is more than 30 years old, PostgreSQL has experienced strong growth in recent years thanks to incrementally improving features and a variety of DBaaS systems available. According to DB Engines, which collects data from Google as well as job ads and website mentions, PostgreSQL has increased its market presence by 35 percent over September last year and ranks fourth behind Oracle, MySQL and Microsoft SQL Server.
Other new features that developers using or considering PostgreSQL can look forward to is logical replication, which gives developers greater control over columns they want to replicate and those they don’t want to replicate, a move that could find use cases in data security, Shahid said. ®
https://www.theregister.com/2022/10/03/postgresql_15/ PostgreSQL suggests better Oracle and SQL Server migrations • The Register