Roland Takacs wrote an interesting blog post titled Oracle vs PostgreSQL: First Glance earlier this month. The genesis for his blog post was that he was in the middle of migrating his current Oracle tech stack to Python, parquet files, and PostgreSQL. As such, Roland thought it might be a good exercise to document the various Oracle features he was accustomed to and figure out what the equivalent functionality was in PostgreSQL. In his post, Roland walked us through 15 different Oracle features and their PostgreSQL equivalents.

Seeing that YugabyteDB is a PostgreSQL-compatible, distributed SQL database (in fact it reuses PostgreSQL’s native query layer), I thought it would be an interesting exercise to verify which of the “Oracle functional equivalents” in PostgreSQL that Roland highlighted would also work in YugabyteDB. YugabyteDB supports more SQL features than any other distributed SQL database, including CockroachDB. In this post I’ll walk you through each of the features from Roland’s post so we can see how YugabyteDB stacks up on the “PostgreSQL compatibility” vector.

What’s YugabyteDB?_ It is an open source, high-performance distributed SQL database built on a scalable and fault-tolerant design inspired by Google Spanner. Yugabyte’s SQL API (YSQL) is PostgreSQL wire compatible._

If you’d like to follow along and test the exercises against YugabyteDB, you can either install a cluster locally or sign up for a free cluster on Yugabyte Cloud.

DUAL Table

Oracle

Oracle ships with a table called DUAL which can be thought of as a dummy table with a single record that can be used for selecting when you’re not actually interested in the data, but instead want the results of some system function in a SELECT statement. For example:

SELECT 1*3 FROM dual;

PostgreSQL

The DUAL table doesn’t exist in PostgreSQL, but you can achieve similar results by excluding the FROM clause.

SELECT 1*3;

YugabyteDB

YugabyteDB doesn’t have a DUAL table either, but supports the same functionality as PostgreSQL.

SELECT 1*3;

Result:

3

String Concatenation

Oracle

'Concat with ' || NULL

Result:

Concat with

PostgreSQL

To achieve the same Oracle functionality in PostgreSQL, we’ll need to use the concat function:

SELECT concat('Concat with ', NULL);

Result:

Concat with

YugabyteDB

YugabyteDB can perform string concatenation just like PostgreSQL.

SELECT concat('Concat with ', NULL);

Result:

Concat with

ROWNUM and ROWID

Oracle

ROWNUM is a pseudocolumn which indicates the row number in a result set retrieved by a SQL query. Likewise, ROWID is a pseudocolumn that returns the address of the row.

SELECT rowid,
       rownum,
       country
FROM country
WHERE rownum <= 5;

PostgreSQL

In PostgreSQL, you can execute something like this:

SELECT ctid AS rowid,
       row_number() over() AS rn,
       country 
FROM country
LIMIT 5;

YugabyteDB

In PostgreSQL, ROW_NUMBER() function is a window function that assigns a sequential integer to each row in a result set. We can perform a similar query on the us_states table in the Northwind sample database and get the same results.

SELECT row_number() over() AS rn,
       state_name 
FROM us_states
LIMIT 5;

Result:

YugabyteDB currently doesn’t expose a PostgreSQL system column like ctid, so for now, Oracle’s ROWID functionality is not supported.

#databases #distributed sql #how to #open source #postgresql #oracle