Postgresql regex to match uppercase, Unicode-aware

Question

The title sums it up pretty well. I'm looking for a regular expression matching Unicode uppercase character for the Postgres ~ operator. The obvious way doesn't work:

=> select 'A' ~ '[[:upper:]]';
 ?column? 
----------
 t
(1 row)

=> select 'Ó' ~ '[[:upper:]]';
 ?column? 
----------
 t
(1 row)

=> select 'Ą' ~ '[[:upper:]]';
 ?column? 
----------
 f
(1 row)

I'm using Postgresql 9.1 and my locale is set to pl_PL.UTF-8. The ordering works fine.

=> show LC_CTYPE;
  lc_ctype   
-------------
 pl_PL.UTF-8
(1 row)

not a proper answer, but Ą matches [[:upper:]] on my local postgresql 9.2.1 (but not 9.1.6) — araqnid
– araqnid, Commented Jan 11, 2013 at 12:27
@araqnid Matches for me also in 9.2. What if you try with the collation in 9.1?: select 'Ą' ~ '[[:upper:]]' collate "pl_PL" — Clodoaldo Neto
– Clodoaldo Neto, Commented Jan 11, 2013 at 12:31
@Clodoaldo explicitly specifying the collation makes no difference to the results — araqnid
– araqnid, Commented Jan 11, 2013 at 12:38
May be related to bug #6457 that got fixed when it was reported (but not mentioned in release notes as far as I can see) — Daniel Vérité
– Daniel Vérité, Commented Jan 11, 2013 at 14:22

Daniel Vérité · Accepted Answer · 2013-01-12 13:48:33Z

4

The regexp engine of PG 9.1 and older versions does not correctly classify characters whose codepoint doesn't fit it one byte. The codepoint of 'Ó' being 211 it gets it right, but the codepoint of 'Ą' is 260, beyond 255.

PG 9.2 is better at this, though still not 100% right for all alphabets. See this commit in PostgreSQL source code, and particularly these parts of the comment:

remove the hard-wired limitation to not consider wctype.h results for character codes above 255

and

Still, we can push it up to U+7FF (which I chose as the limit of 2-byte UTF8 characters), which will at least make Eastern Europeans happy pending a better solution

Unfortunately this was not backported to 9.1

answered Jan 12, 2013 at 13:48

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

etam1024 · Accepted Answer · 2013-06-09 18:27:44Z

1

I've found that perl regular expressions handles Unicode perfectly.

create extension plperl;

create function is_letter_upper(text) returns boolean
immutable strict language plperl
as $$
    use feature 'unicode_strings';
    return $_[0] =~ /^\p{IsUpper}$/ ? "true" : "false";
$$;

Tested on postgres 9.2 with perl 5.16.2.

answered Jun 9, 2013 at 18:27

etam1024

8431 gold badge7 silver badges18 bronze badges

Collectives™ on Stack Overflow

Postgresql regex to match uppercase, Unicode-aware

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related