Different sort results between Oracle and Postgres with Special Characters-CodePudding

*Apologies as I know this question has been asked before, but tried the previously suggested approaches and none fixed my issue.

I am trying to compare a table found in two databases.

COLUMN_VALUE
01.00^01.13
01.00 01.12
01.00 01.15

Unfortunately, the table does not have a primary key, so I am trying to order by the same column and compare that way.

When I sort the above table in Oracle, it sorts to:

SELECT * FROM (
SELECT '01.00 01.12' AS "COLUMN_VALUE" FROM DUAL
UNION ALL
SELECT '01.00^01.13' AS "COLUMN_VALUE" FROM DUAL
UNION ALL
SELECT '01.00 01.15' AS "COLUMN_VALUE" FROM DUAL) ORDER BY 1;

COLUMN_VALUE
01.00 01.12
01.00 01.15
01.00^01.13

However, when I perform the sort in Postgres, it sorts to:

SELECT * FROM (
SELECT '01.00 01.12' AS "COLUMN_VALUE"
UNION ALL
SELECT '01.00^01.13' AS "COLUMN_VALUE" 
UNION ALL
SELECT '01.00 01.15' AS "COLUMN_VALUE") T1 ORDER BY 1;

COLUMN_VALUE
01.00 01.12
01.00^01.13
01.00 01.15

This issue appears to be how the ^ character is sorted. Does anyone know a workaround so that both databases sort the information the same way?

CodePudding user response：

Creating hash values and using them for ordering seems to be an option.

Test tables (Postgresql / Oracle)

Query (Postgresql) - order by MD5 hash

select c_, md5( c_ )
from test
order by 2 ;

-- result
c_              md5
01.00 01.12     335dca42dedecedc19ba65065a7777ec
01.00^01.13     4e1661df191eb006274be5552a998280
01.00 01.15     c1197d14549263a867fd9850f42b68b1

Query (Oracle) - order by MD5 hash

select c_, standard_hash( c_ , 'MD5' )
from test
order by 2 ;

C_              STANDARD_HASH(C_,'MD5')
01.00 01.12     0x335DCA42DEDECEDC19BA65065A7777EC
01.00^01.13     0x4E1661DF191EB006274BE5552A998280
01.00 01.15     0xC1197D14549263A867FD9850F42B68B1

If the MD5 is not good enough, you can also use SHA256, which will probably give you a different ordering, but the same order result for both Postgresql and Oracle. Examples on DBfiddle, for Postgresql 12 and Oracle 18c.

CodePudding user response：

Postgres:

SELECT * FROM (
SELECT '01.00 01.12' AS "COLUMN_VALUE" 
UNION ALL
SELECT '01.00^01.13' AS "COLUMN_VALUE" 
UNION ALL
SELECT '01.00 01.15' AS "COLUMN_VALUE") T1 ORDER BY "COLUMN_VALUE" COLLATE "C";

 COLUMN_VALUE
--------------
 01.00 01.12
 01.00 01.15
 01.00^01.13
(3 rows)

I know this question has been asked before, but tried the previously suggested approaches and none fixed my issue.

Well, the answer is in Different behaviour in "order by" clause: Oracle vs. PostgreSQL