Oracle vs Postgres order by

Question

I am running below query in Oracle and Postgres, both shows different output with respect to ordering of the values.

with test as (
select 'Summary-Account by User (Using Contact ID)' col1 from dual
    union all
select 'Summary-Account by User by Client by Day (Using Contact ID)' col1 from dual
)
select * from test
order by col1 desc;

Below is Oracle one

Postgres

with test as (
select 'Summary-Account by User (Using Contact ID)' col1
    union all
select 'Summary-Account by User by Client by Day (Using Contact ID)' col1
)
select * from test
order by col1 desc;

Oracle collation is AL32UTF8 Postgres has LC_CTYPS is en_US.UTF-8

Both of them look same from how database should behave. How to fix this?

I have read few posts on stackoverflow about POSIX and C, after changing the query order by to order by col1 collate "C" desc; The result matches Oracle output.

Is there anyway to apply this permanently?

Answer 1

AL32UTF8 is not a collation, but an encoding (character set).

Oracle uses the “binary collation” by default, which corresponds the the C or POSIX collation in PostgreSQL.

You have several options to get a similar result in PostgreSQL:

• create the database with LOCALE "C"

• if you are selecting from a table, define the column to use the "C" collation:

  ALTER TABLE tab ALTER col1 TYPE text COLLATE "C";
  

• add an explicit COLLATE clause:

  ORDER BY col1 COLLATE "C"