1

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.

  • 1
    What is printable depends on how your database was set up. What is the "ctype" of your database? (\l in psql) – jjanes Aug 23 '19 at 0:42
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)
|improve this answer|||||
  • Thanks again; you provided what I needed to find an answer. I think [\cA-\cZ] is essentially [^[:print:]] and will work for me. – user2797314 Aug 23 '19 at 16:23
  • So you think "ESC" is printable? select regexp_replace(E'ni\u001Bo', '[\cA-\cZ]', '[X]','g'); What's wrong with using COLLATE? – Laurenz Albe Aug 23 '19 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.