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.
After having the first wider look at PostgreSQL, I decided to gather some of the differences and point them out in this article. I am planning to share my experience during and after the migration as well.
Our often used
DUAL table from Oracle doesn’t exist in PostgreSQL. Don’t worry though, you may even find the other way simpler.
SELECT 1*3 FROM dual;
'Concat with ' || NULL
Result in Oracle:
Result in PostgreSQL:
To achieve the same functionality in PostgreSQL, use the
concat('Concat with ', NULL)
SELECT rowid, rownum, country FROM country WHERE rownum <= 5;
SELECT ctid AS rowid, row_number() over() AS rn, country FROM country LIMIT 5;
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.
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;
We could go on and on with all the different database objects.
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.
SELECT * FROM countries c, locations l WHERE c.country_id = l.country_id (+);
SELECT * FROM countries c LEFT OUTER JOIN locations l ON c.country_id = l.country_id;
Postgre’s solution also works in Oracle.
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.
UPDATE countries SET country_name = upper(country_name) WHERE country_id IN (SELECT country_id FROM locations);
UPDATE city SET city = upper(city) FROM country WHERE city.country_id = country.country_id;
The same way as described before with
UPDATE, in PostgreSQL we can simply use values from another table when we execute a
DELETE FROM locations WHERE country_id in (SELECT country_id FROM countries);
DELETE FROM city USING country WHERE city.country_id = country.country_id;
Oracle’s solution also works in PostgreSQL.
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
In the following example we insert a new record or update the email address if the
name columns match.
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);
INSERT INTO customers (name, email) VALUES ('Somebody', 'email@example.com') ON CONFLICT (name) DO UPDATE SET email = customers.email;
Both in Oracle or in PostgreSQL we can choose to do nothing if there is a match (conflict).
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;
This solution also works in PostgreSQL.
Unlike in Oracle,
TRUNCATE is transaction-safe in PostgreSQL. It means that if you place it within the transaction statements such as
ROLLBACK, the truncation operation will be rolled back safely.
The commonly used
PRIOR clauses from Oracle don’t exist in PostgreSQL. At least not by default. By installing the
tablefunc extension you can have equivalent functions. Also, in PostgreSQL you can use recursive Common Table Expressions (CTE) to achieve the same result. (CTEs are also available in Oracle.)
You can find more information with examples here.
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.
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) );
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);
SELECT * FROM sales_p_america;
Normally we organize our code into blocks, procedures or functions. Let’s have a look at the differences.
DECLARE ... variables BEGIN ... code to be executed EXCEPTION ... exception handling END;
DO $$ DECLARE ... variables BEGIN ... code to be executed EXCEPTION .. exception handling END $$;
The body of a block, procedure or function is passed as a string literal. To avoid having to write the code as a long string and having to escape all single quotes, PostgreSQL offers us the
$$ syntactic sugar that indicates a single quote. If we wanted, however, we could still use a single quote instead of
The syntax difference is the same with regards to procedures and functions as well. You can have a deeper look here and here.
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.
The following procedure name would substitute Oracle’s
Although it is very important to highlight that even if we can “organize” our PostgreSQL procedures and functions by using this standard naming convention, unfortunately we can forget about the package level global variables.
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.