Is there a way to have PostgreSQL consider the Interpunct character · as a letter? I'm working on an application storing strings of the Gallo language. In this language the Interpunct character · is considered a letter, not a word divider.

So I want for instance the query

SELECT * FROM records WHERE sentence ~* '\m·r'


to return records where sentence contains word starting with ·r.

Is there a way to force PosgresSQL to consider this Interpunct character ·  as a letter?
Phew... took ages, but I finally figured it out! It's a bit of a hack, but it was the only way I could find to do it.

My train of thought went like this:

SELECT ASCII('·'); Result: 183

After a lot of experimentation (see exhaustive fiddle here), I came up with this:

WITH cte AS
(
  SELECT CHR(112) || CHR(104) || CHR(183) || CHR(97) AS my_string
)
SELECT my_string FROM cte;


Result:

my_string
ph·a


which, as you can see, contains an interpunct character. I was forced to use the ASCII number representations because dbfiddle.uk doesn't appear to treat · - interpunct - as a "proper" letter.

So, then I did this:

WITH cte AS
(
  SELECT CHR(112) || CHR(104) || CHR(183) || CHR(97) AS my_string
)
SELECT 'ph·a' ~* (SELECT my_string FROM cte) AS test_result;


Result:

test_result
t


Which is the correct answer.

I know that it's a lot of trouble to go to (believe me, I was beating my head off the table betimes! :-) ), but it was the only way that I could figure it out. It is possible that with the right character set (utf8mtb4) or similar, interpunct may be treated as a proper letter - but it's the best I could do for the moment! HTH &amp; A+! p.s. +1 for the question BTW, v. interesting and I learnt a lot! :-)