PostgreSQL 15 promises to ease Oracle and SQL Server migrations
MERGE means more code can be copied from earlier installation, because let's be real – that's how it's happening
PostgreSQL, the popular open source relational database, is getting support for MERGE statements, a move which is intended to make migration from SQL Server and Oracle-based systems easier.
With the release candidate for PostgreSQL 15 published on Friday and general availability expected on 13 October, the upgrade promises a slew of new features for developers.
But for those eyeing migration from Oracle to PostgreSQL, the new MERGE implementation that might 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 consultancy and software firm Percona, said: "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."
Open source databases: What are they and why do they matter?READ MORE
As well as meaning fewer lines of code for the same operations, PostgreSQL's support for MERGE is expected to make it easier for Oracle and SQL Server migrations.
"The MERGE statement is helping users to be able to migrate relatively easily from Oracle to Postgres, which is an ever-popular use case," Shahid said.
He argued the SQL code that is already written in Oracle and in SQL Server could be ported to PostgreSQL because all three databases are ANSI-compliant.
"The code tends to look fairly similar. Most of the code can just work as long as the functionality exists in the database. [Before PostgreSQL 15] the developers who have used the MERGE functionality in Oracle already would otherwise have had to rewrite that code after move to PostgreSQL. With this feature, they don't need to do that. The existing code in Oracle will just work," Shahid said.
Postgres was first proposed by Michael Stonebraker and Lawrence Rowe of UC Berkeley in 1986 [PDF] as a successor to Ingres, and is currently available under the PostgreSQL License, an open source license, similar to the BSD or MIT licenses.
- Cockroach Labs CTO: Google became too comfortable, I wasn't being challenged
- Oracle Cloud at one point would let you access any other customer's data
- Google previews streaming connector for BigQuery
- YugabyteDB tempts devs with read-committed isolation levels
Although a database more than 30 years old, PostgreSQL has enjoyed a lot of growth in the last few years, thanks to gradually improving features and a host of available DBaaS systems. According to DB-Engines, which collates data from Google, as well as job ads and website mentions, PostgreSQL has increased its market profile by 35 per cent compared with September last year and is fourth in the ranking behind Oracle, MySQL and Microsoft SQL Server.
Other new features developers using or contemplating PostgreSQL can look forward to include logical replication, which offers devs greater control over columns they want to replicate and those they do not, a move which could find use cases in data security, Shahid said. ®