regex character class :print: in postgres

Question

I am trying to replicate an Oracle check constraint that prevents non-printing characters in postgres, and I am stumped. I think postgres may be treating :print: and :ascii: as synonymys?!?

select REGEXP_REPLACE('bla', '[^[:print:]]', '[X]','g') ;
 regexp_replace 
----------------
 bla
(1 row)

select REGEXP_REPLACE('bla'||chr(10)||'bla', '[^[:print:]]', '[X]','g') ;
 regexp_replace 
----------------
 bla[X]bla
(1 row)

select REGEXP_REPLACE('Ҕ', '[^[:print:]]', '[X]','g') ;
 regexp_replace 
----------------
 [X]
(1 row)

select REGEXP_REPLACE('ñino', '[^[:print:]]', '[X]','g') ;
 regexp_replace 
----------------
 [X]ino
(1 row)

Why would Ҕ and ñ be caught in that?

Any guidance is appreciated.

Answer 1

It is the LC_CTYPE setting that defines character classification, case conversion, and other character attributes. So this is what determines if a character is printable or not.

ñ is not printable in the C locale:

SHOW lc_ctype;

 lc_ctype 
----------
 C
(1 row)

select regexp_replace('niño', '[^[:print:]]', '[X]','g');

 regexp_replace 
----------------
 ni[X]o
(1 row)

But it is printable in de_DE.utf8:

SHOW lc_ctype;

  lc_ctype  
------------
 de_DE.utf8
(1 row)

select regexp_replace('niño', '[^[:print:]]', '[X]','g');

 regexp_replace 
----------------
 niño
(1 row)

If you don't want to change the lc_ctype of the database (which can only be done with dump/restore), you can also use the COLLATE clause to give a string expression a different collation (and character type):

select regexp_replace('niño' COLLATE "en_US.utf8", '[^[:print:]]', '[X]','g');

 regexp_replace 
----------------
 niño
(1 row)

You see that ñ is also recognized as a printable character in the English UNICODE collation, but you can also use the language independent ICU collation if you have built PostgreSQL with ICU support:

select regexp_replace('niño' COLLATE "und-x-icu", '[^[:print:]]', '[X]','g');

 regexp_replace 
----------------
 niño
(1 row)