1

*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?

DJC
  • 1,491
  • 6
  • 19
  • 1
    since there is not PK, comparing the values via computed SHA2 (or higher) digests, may be better option. As long as counts match and the SHA2 of the values match - that should provide proof of equality. – PrasadU Sep 20 '21 at 17:33
  • 2
    No - if you run that exact code in PostgreSQL, you don't get the result you posted. You get the same result as in Oracle. (I just tested it on DB Fiddle.) As for your true question: you said the data resides in a table, it is not created on the fly as in your example. If so, what is the **data type** of the column (in the Postgre table) that holds these values? –  Sep 20 '21 at 17:54
  • Can you clarify exactly what other solutions you've tried, so that people don't have to waste time suggesting them to you again? – IMSoP Sep 21 '21 at 20:03

2 Answers2

1

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

Test tables (Postgresql / Oracle)

enter image description here enter image description here

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.

stefan
  • 2,182
  • 2
  • 13
  • 14
1

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

klin
  • 112,967
  • 15
  • 204
  • 232
  • Thanks. I tried this but for some reason I was still getting different sort results in my actual use case so not sure what is going on. – DJC Sep 22 '21 at 13:39