Postgres 9.0 + translate function and ascii codes

Question

I use the translate function to process searches accent insensitive. To improve this request, i've created a matching index :

CREATE INDEX person_lastname_ci_ai_si
ON person
USING btree
(translate(upper(lastname::text), '\303\200\303\201\303\202\303\203\303\204\303\205\303\206\303\207\303\210\303\211\303\212\303\213\303\214\303\215\303\216\303\217\303\221\303\222\303\223\303\224\303\225\303\226\303\230\303\231\303\232\303\233\303\234\303\235\303\237\303\240\303\241\303\242\303\243\303\244\303\245\303\246\303\247\303\250\303\251\303\252\303\253\303\254\303\255\303\256\303\257\303\261\303\262\303\263\303\264\303\265\303\266\303\270\303\271\303\272\303\273\303\274\303\275\303\277'::text, 'AAAAAAACEEEEIIIINOOOOOOUUUUYSaaaaaaaceeeeiiiinoooooouuuuyy'::text)
);

It works fine with postgres 9.1 but it seems to don't work with 9.0. Postgres 9.0 seems to replace

'\303\200\303\201\303\202\303\203\303\204\303\205\303\206\303\207\303\210\303\211\303\212\303\213\303\214\303\215\303\216\303\217\303\221\303\222\303\223\303\224\303\225\303\226\303\230\303\231\303\232\303\233\303\234\303\235\303\237\303\240\303\241\303\242\303\243\303\244\303\245\303\246\303\247\303\250\303\251\303\252\303\253\303\254\303\255\303\256\303\257\303\261\303\262\303\263\303\264\303\265\303\266\303\270\303\271\303\272\303\273\303\274\303\275\303\277'

by

ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜÝßàáâãäåæçèéêëìíîïñòóôõöøùúûüýÿ

Then, because my code perform searches using ascii codes, it doesn't use the index..

Is there a way to avoid postgres to convert ascii codes to characters when creating index ?

For example :

select '\303\200\303\201\303\202\303\203\303\204\303\205\303\206\303\207\303\210\303\211\303\212\303\213\303\214\303\215\303\216\303\217\303\221\303\222\303\223\303\224\303\225\303\226\303\230\303\231\303\232\303\233\303\234\303\235\303\237\303\240\303\241\303\242\303\243\303\244\303\245\303\246\303\247\303\250\303\251\303\252\303\253\303\254\303\255\303\256\303\257\303\261\303\262\303\263\303\264\303\265\303\266\303\270\303\271\303\272\303\273\303\274\303\275\303\277'

;

Result

ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖØÙÚÛÜÝßàáâãäåæçèéêëìíîïñòóôõöøùúûüýÿ

How can i have this result ?

\303\200\303\201\303\202\303\203\303\204\303\205\303\206\303\207\303\210\303\211\303\212\303\213\303\214\303\215\303\216\303\217\303\221\303\222\303\223\303\224\303\225\303\226\303\230\303\231\303\232\303\233\303\234\303\235\303\237\303\240\303\241\303\242\303\243\303\244\303\245\303\246\303\247\303\250\303\251\303\252\303\253\303\254\303\255\303\256\303\257\303\261\303\262\303\263\303\264\303\265\303\266\303\270\303\271\303\272\303\273\303\274\303\275\303\277

Mike Sherrill 'Cat Recall' · Accepted Answer · 2012-04-13 10:15:11Z

1

Starting from version 9.1, PostgreSQL standard_conforming_strings option defaults to ON.

This means that backslash \ character is treated as-is and not as escaping symbol, this is done to prevent SQL injection attacks; this follows SQL standard recommendations. It is still possible to use \ to get special characters, but only within string constants.

For the pre-9.1 versions of PostgreSQL I suppose these options are possible:

Change system-wide standard_conforming_strings option to ON, but this will affect whole cluster and may give unexpected results in other areas;
Change standard_conforming_strings option on a per-user basis, using ALTER ROLE ... SET standard_conforming_strings TO on;, this one also may have side effects;
Use plain SET standard_conforming_strings TO on; as a first command you issue in your session before creating the index;
Double all your backslashes so that are treated as a literal \ symbol in your CREATE INDEX ... statement.

Let me know if this helps.

edited Apr 13, 2012 at 10:15

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

answered Apr 13, 2012 at 9:58

vyegorov

23k7 gold badges61 silver badges76 bronze badges

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

2 Comments

Valéry Stroeder Over a year ago

Thanks for your help. select '\303\200\303\201\303\202\303(...)' return '\303\200\303\201\303\202\303(...)' -> good. But create index person_firstnamebegin_ci_ai_si ON person USING btree (translate(substr(upper(replace(firstname::text, ' '::text, ''::text)), 1, 1), '\\303\\200\\303\\201\\303\\202\\303\\203\\303\\204\\303(...) create an index with doubled slash values instead of simple slash even if I create index with single slashes

Valéry Stroeder Over a year ago

PGADMIN displays index with double slahes instead of simple slashes but my searches uses the created index well. Thanks for your help

Collectives™ on Stack Overflow

Postgres 9.0 + translate function and ascii codes

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related