<p></p>
<p dir="auto">I am strongly in favour of requiring 12. I doubt parts of the toolset work on earlier versions, with osmdbt requiring logical replication. Recent postgres versions are easily available with pgdg on Ubuntu, Debian, and RHEL-based systems.</p>
<p dir="auto">I was researching another way to do it which right now is equivalent in functionality, but could be much better under PostgreSQL 16.</p>
<p dir="auto">PostgreSQL 12 added <a href="https://www.postgresql.org/docs/current/collation.html#COLLATION-NONDETERMINISTIC" rel="nofollow">non-deterministic collations</a> with an index created on that collation, then you get <code class="notranslate">SELECT 'n' = 'ñ COLLATE usernames;</code> returning true and using an index.</p>
<p dir="auto">Something this would create a suitable collation</p>
<div class="highlight highlight-source-sql" dir="auto"><pre class="notranslate">CREATE COLLATION usernames (
provider <span class="pl-k">=</span> icu,
deterministic <span class="pl-k">=</span> false, 
locale <span class="pl-k">=</span> <span class="pl-s"><span class="pl-pds">'</span>und-u-ka-shifted-kk-ks-level1<span class="pl-pds">'</span></span>
);</pre></div>
<p dir="auto">This would only look at the base character, case insensitive. e.g. <code class="notranslate">'N' = 'ñ'</code>.</p>
<p dir="auto">Where this approach shines is under PostgreSQL 16, where you can add tailoring rules which set equality differently.</p>
<div class="highlight highlight-source-sql" dir="auto"><pre class="notranslate">CREATE COLLATION coll1 (
provider <span class="pl-k">=</span> icu,
deterministic <span class="pl-k">=</span> false,
locale <span class="pl-k">=</span> <span class="pl-s"><span class="pl-pds">'</span>und<span class="pl-pds">'</span></span>,
rules <span class="pl-k">=</span> <span class="pl-s"><span class="pl-pds">'</span>& a = b<span class="pl-pds">'</span></span>);
<span class="pl-k">SELECT</span> <span class="pl-s"><span class="pl-pds">'</span>a<span class="pl-pds">'</span></span> <span class="pl-k">=</span> <span class="pl-s"><span class="pl-pds">'</span>b<span class="pl-pds">'</span></span> COLLATE coll1;
 ?column?
──────────
 t
(<span class="pl-c1">1</span> row)</pre></div>
<p dir="auto">I'm still trying to figure out how to start with a locale other than a base locale when adding rules, as well as how to handle all the quoting needed when every character you care about is a homograph to another.</p>

<p style="font-size:small;-webkit-text-size-adjust:none;color:#666;">—<br />Reply to this email directly, <a href="https://github.com/openstreetmap/openstreetmap-website/pull/4405#issuecomment-1865979238">view it on GitHub</a>, or <a href="https://github.com/notifications/unsubscribe-auth/AAK2OLLUOHQ5BNPYQPZZWK3YKQCMPAVCNFSM6AAAAABATZVAXOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNRVHE3TSMRTHA">unsubscribe</a>.<br />You are receiving this because you are subscribed to this thread.<img src="https://github.com/notifications/beacon/AAK2OLKMSJW2D24G5XBULKDYKQCMPA5CNFSM6AAAAABATZVAXOWGG33NNVSW45C7OR4XAZNMJFZXG5LFINXW23LFNZ2KUY3PNVWWK3TUL5UWJTTPHCKWM.gif" height="1" width="1" alt="" /><span style="color: transparent; font-size: 0; display: none; visibility: hidden; overflow: hidden; opacity: 0; width: 0; height: 0; max-width: 0; max-height: 0; mso-hide: all">Message ID: <span><openstreetmap/openstreetmap-website/pull/4405/c1865979238</span><span>@</span><span>github</span><span>.</span><span>com></span></span></p>
<script type="application/ld+json">[
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"potentialAction": {
"@type": "ViewAction",
"target": "https://github.com/openstreetmap/openstreetmap-website/pull/4405#issuecomment-1865979238",
"url": "https://github.com/openstreetmap/openstreetmap-website/pull/4405#issuecomment-1865979238",
"name": "View Pull Request"
},
"description": "View this Pull Request on GitHub",
"publisher": {
"@type": "Organization",
"name": "GitHub",
"url": "https://github.com"
}
}
]</script>