EBCDIC sorting as a use case for ICU rules

From: "Daniel Verite" <daniel(at)manitou-mail(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Jeff Davis <pgsql(at)j-davis(dot)com>, Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)com>, Jonathan S(dot) Katz <jkatz(at)postgresql(dot)org>
Subject: EBCDIC sorting as a use case for ICU rules
Date: 2023-06-21 13:28:38
Message-ID: 35cc1684-e516-4a01-a256-351632d47066@manitou-mail.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


In the "Order changes in PG16 since ICU introduction" discussion, one
sub-thread [1] was about having a credible use case for tailoring collations
with custom rules, a new feature in v16.

At a conference this week I was asked if ICU could be able to
sort like EBCDIC [2]. It turns out it has been already asked on
-general a few years ago [3] with no satisfactory answer at the time ,
and that it can be implemented with rules in v16.

A collation like the following this seems to work (the rule simply enumerates
US-ASCII letters in the EBCDIC alphabet order, with adequate quoting)

CREATE COLLATION ebcdic (provider='icu', locale='und',

This can be useful for people who migrate from mainframes to Postgres
and need their migration tests to produce the same sorted results as the
original system.
Since rules can be defined at the database level with the icu_rules option,
they don't even need to tweak their queries to add COLLATE clauses,
which surely is appreciable in that kind of project.

US-ASCII when sorted in EBCDIC order comes out like this:


Maybe this example could be added to the documentation except for
the problem that the rule is very long and dollar-quoting cannot be split
into several lines. Literals enclosed by single quotes can be split that
way, but would require escaping the single quotes in the rule, which
would lead to scary-looking over-quoted contents.

I'm open to suggestions on whether this EBCDIC example is worth being in the
doc in some form or putting this in the wiki would be good enough.


[2] https://en.wikipedia.org/wiki/EBCDIC


Best regards,
Daniel Vérité
Twitter: @DanielVerite


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2023-06-21 13:43:38 Re: ProcessStartupPacket(): database_name and user_name truncation
Previous Message Tomas Vondra 2023-06-21 13:28:11 Re: Can JoinFilter condition be pushed down into IndexScan?