Friday 23 February 2024

Migrating Oracle to PostgreSQL

So at work we're currently trying to see if we can move away from Oracle and onto PostgreSQL.

I expect this to be a long process.

This blog post is here to document some of the issues we ran into and also remember references I found.

Migrating schema and data

There are several tools that can migrate the schema and the data from Oracle to Postgres.

There are some differences in what both database support. See reference [1].

Make changes to SQL Scripts

References

[1] Hackermoon - How to Migrate from Oracle to PostgreSQL
https://hackernoon.com/how-to-migrate-from-oracle-to-postgresql
Postgres Wiki - Oracle to Postgres Conversion
https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion
PostgreSQL Manuals
https://www.postgresql.org/docs/
Ora2Pg - Moves Oracle and MySQL database to PostgreSQL
https://ora2pg.darold.net/
Life in USA - install ora2pg on mac osx
https://vcfvct.wordpress.com/2016/10/18/install-ora2pg-on-mac-osx/

Friday 12 January 2024

SQL: Concat operator and Spaces

So I had a pressing need to query my database and concatenate several values from a row together (using either concat or ||), but with spaces in between.

But I don't want two spaces if the value in between is empty.

It is surprisingly difficult to do in SQL, but there are some options available.

First the data:

TITLEFIRSTNAMEMIDDLENAMEFAMILYNAME
IchabodCrane
Dr.ThomasLancaster
PhilipMartinBrown

0. No solution

select TITLE || ' ' || FIRSTNAME || ' ' || MIDDLENAME || ' ' || FAMILYNAME from PERSON;

So, if you use the SQL statement above, you get the problem, extraneous spaces. For example "[ ]Ichabod[ ][ ]Crane". We wish to eliminate those.

1. COALESCE

select coalesce(TITLE || ' ','') || coalesce(FIRSTNAME || ' ','') || coalesce(MIDDLENAME || ' ','') || FAMILYNAME from PERSON;

This is a good try, but doesn't work, as the first expression of the coalesce is never NULL, because of the concat used with the space (|| ' ') used.

1. DECODE

select decode(TITLE, null, '', TITLE, TITLE || ' ') ||
       decode(FIRSTNAME, null, '', FIRSTNAME, FIRSTNAME || ' ') ||
       decode(MIDDLENAME, null, '', MIDDLENAME, MIDDLENAME || ' ') ||
       FAMILYNAME
       from PERSON;

This works but is a lot of code to do something very simple.

2. CASE

select (case
           when TITLE is null then ''
           else TITLE || ' ' end) ||
       (case
           when FIRSTNAME is null then ''
           else FIRSTNAME || ' ' end) ||
       (case
           when MIDDLENAME is null then ''
           else MIDDLENAME || ' ' end) ||
       FAMILYNAME
from PERSON;

This works but is a lot of code. Luckily the case statement, when indented properly, is quite readable and not at all bad.

3. NVL2

select nvl2(TITLE, TITLE || ' ', '') ||
       nvl2(FIRSTNAME, FIRSTNAME || ' ', '') ||
       nvl2(MIDDLENAME, MIDDLENAME || ' ', '') ||
       FAMILYNAME from PERSON;

This works but is hard to read. Requires knowledge of nvl2.

4. LTRIM

select ltrim(TITLE || ' ') ||
       ltrim(FIRSTNAME || ' ') ||
       ltrim(MIDDLENAME || ' ') ||
       FAMILYNAME from PERSON;

As far as I can tell, this is quite appropriate. The ltrim removes spaces at the beginning of the string. If the string contains only ' ', all ' ' are removed and we're home free.

It is short, but requires some knowledge of what ltrim does exactly.

Falls in the category of 'doing something clever'.

5. REGEXP_REPLACE

select regexp_replace(TITLE
        || ' ' || FIRSTNAME
        || ' ' || MIDDLENAME
        || ' ' || FAMILYNAME
        , ' +', ' ')
from person;

A colleague of mine came up with this little chestnut.

It's nice if you enjoy regular expressions. I do not.

Still, it's nice to be able to just concat everything together, and have the whole thing sorted out with one simple regular expression.

Addendum

The function wm_concat has been removed from Oracle PL/SQL and should not be used.

Which is a shame, as it does exactly what we want.

References

American Culture - Naming
https://culturalatlas.sbs.com.au/american-culture/american-culture-naming
Lalit Kumar B - Why not use WM_CONCAT function in Oracle?
https://lalitkumarb.wordpress.com/2015/04/29/why-not-use-wm_concat-function-in-oracle/

Updates: added regexp_replace solution