We are facing some interesting times at work as we are going to start changing our technology stack in the near future. Most of our Oracle processes will be replaced by Python, processing the data stored in parquet files. For indispensable database processes we will change to PostgreSQL. I like Oracle and it has served us well, but I am excited about learning and working with new technologies. This is a great opportunity for us to take part in building something from the very beginning and gain experience in other technologies.

DUAL Table

Our often used DUAL table from Oracle doesn’t exist in PostgreSQL. Don’t worry though, you may even find the other way simpler.

Oracle:

SELECT 1*3 FROM dual;

PostgreSQL:

SELECT 1*3;

String Concatenation

'Concat with ' || NULL

Result in Oracle:

Concat with 

Result in PostgreSQL:

[null]

To achieve the same functionality in PostgreSQL, use the concat function:

concat('Concat with ', NULL)

ROWNUM and ROWID

Oracle:

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

PostgreSQL:

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

IF EXISTS for DDL operations

I really miss this from Oracle, but luckily PostgreSQL has the ability to check whether or not a database object exists before running a DDL operation on it.

Some examples:

CREATE TABLE IF NOT EXISTS table_name (...);
DROP TABLE IF EXISTS table_name;
ALTER TABLE IF EXISTS table_name RENAME TO new_name;
ALTER TABLE table_name DROP COLUMN IF EXISTS column_name;

Outer Join using (+)

I am confident that anyone who works with Oracle often uses the (+) inside a query to simply force an outer join. In PostgreSQL we don’t have this “luxury”, so we have to go the default way.

Oracle:

SELECT * 
FROM countries c, locations l
WHERE c.country_id = l.country_id (+);

PostgreSQL:

SELECT * 
FROM countries c
LEFT OUTER JOIN locations l ON c.country_id = l.country_id;

UPDATE using another table

Imagine that you want to update values in a table depending on values in another table. In Oracle normally we use a subquery, in PostgreSQL, however, there is a really nice way to do it.

Oracle:

UPDATE countries
SET country_name = upper(country_name)
WHERE country_id IN (SELECT country_id FROM locations);

PostgreSQL:

UPDATE city
SET city = upper(city)
FROM country
WHERE city.country_id = country.country_id;

DELETE using another table

The same way as described before with UPDATE, in PostgreSQL we can simply use values from another table when we execute a DELETE statement.

Oracle:

DELETE FROM locations
WHERE country_id in (SELECT country_id FROM countries);

PostgreSQL:

DELETE FROM city
USING country
WHERE city.country_id = country.country_id;

MERGE (UPSERT)

MERGE is a useful and often used statement that we use to insert or update data depending on its presence. If the data exists, we update the required columns, if not, we insert a new record. In PostgreSQL it is called UPSERT.

In the following example we insert a new record or update the email address if the name columns match.

Oracle:

MERGE INTO customers a
    USING suppliers b
    ON (a.name = b.name)
  WHEN MATCHED THEN
    UPDATE SET a.email = b.email
  WHEN NOT MATCHED THEN
    INSERT (name, email)
    VALUES (b.name, b.email);

PostgreSQL:

INSERT INTO customers (name, email)
VALUES ('Somebody', 'somebody@email.com') 
ON CONFLICT (name) 
DO UPDATE
SET email = customers.email;

Table Inheritance

In PostgreSQL tables can inherit the data and/or the structure from an existing table. You can think of it as the class inheritance in object-oriented programming.

To copy (inherit) both table structure and data:

CREATE TABLE new_table AS 
TABLE existing_table;

To copy (inherit) only the table structure:

CREATE TABLE new_table AS 
TABLE existing_table 
WITH NO DATA;

In Oracle there is a way also to do something similar. However, this solution only creates the table without any additional objects such as indexes or constraints. The newly created table may or may not have data depending on the condition.

CREATE TABLE country_2 AS
SELECT * FROM country;

The above code will create a new table based on country table, with all the data.

If you just want an empty table without data, execute this:

CREATE TABLE country_2 AS
SELECT * FROM country
WHERE 1=0;

TRUNCATE

Hierarchical Queries

Partition Handling

As our team works in the market research industry, the huge volume of data often requires partitioned tables. Let’s have a look at the following example on how to handle partitioning by list.

Oracle:

CREATE TABLE sales (
  salesman_id   INTEGER PRIMARY KEY,
  salesman_name VARCHAR2(30),
  sales_region  VARCHAR2(30),
  sales_date    DATE,
  sales_amount  INTEGER
) 
PARTITION BY LIST (sales_region)
(
  PARTITION p_asia    VALUES ('INDIA','CHINA'),
  PARTITION p_euro    VALUES ('FRANCE','UK'),
  PARTITION p_america VALUES ('USA','CANADA'),
  PARTITION p_rest    VALUES (DEFAULT)
);

PostgreSQL:

CREATE TABLE sales (
  salesman_id   INTEGER,
  salesman_name VARCHAR(30),
  sales_region  VARCHAR(30),
  sales_date    DATE,
  sales_amount  INTEGER
) 
PARTITION BY LIST (sales_region);

CREATE TABLE sales_p_asia    PARTITION OF sales FOR VALUES IN ('INDIA','CHINA');
CREATE TABLE sales_p_euro    PARTITION OF sales FOR VALUES IN ('FRANCE','UK');
CREATE TABLE sales_p_america PARTITION OF sales FOR VALUES IN ('USA','CANADA');
CREATE TABLE sales_p_rest    PARTITION OF sales DEFAULT;

Querying data from a specific partition in Oracle:

SELECT * FROM sales PARTITION (p_america);

In PostgreSQL:

SELECT * FROM sales_p_america;

Block Structure

Normally we organize our code into blocks, procedures or functions. Let’s have a look at the differences.

Oracle:

DECLARE
... variables
BEGIN
... code to be executed
EXCEPTION
... exception handling
END;

PostgreSQL:

DO $$ 
DECLARE
... variables
BEGIN 
... code to be executed
EXCEPTION
.. exception handling
END $$;

Packages

There is no such thing as a package in PostgreSQL. This will probably take some time to get used to after having worked quite some years with Oracle. However, we can organize our package into separate PostgreSQL procedures and functions. (Just like an Oracle package contains separate procedures and functions.) By using a standard naming convention, we can “overcome” this and name our procedures and functions by starting with the imaginary package name followed by two underscores.

package_name__proc_name

The following procedure name would substitute Oracle’s pkg_index.prc_calculate_index procedure:

pkg_index__prc_calculate_index

Conclusion

These are the differences that I found useful to point out at first. As we start migrating to PostgreSQL in the future, I will share our experience and highlight the things to watch out for.

I think we cannot say if one or the other database is better, both of them have their own strengths. As far as the performance is concerned, I will know more after we have completed the migration and started processing production data with PostgreSQL.

If you have any comments or if you have any experience in migrating Oracle PL SQL to PostgreSQL, don’t hesitate to share your thoughts in the comments. I would be happy and interested in hearing them.