how to make text fields accent insensitive?

From: Heine Ferreira <heine(dot)ferreira(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: how to make text fields accent insensitive?
Date: 2012-01-19 19:59:19
Message-ID: CAMuqQjcnC+SgJoHFxTLP-ZqWYiMiGA2fJVNERTXwB1V_5vxvSQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

Recently you answered my question on how to make a field case insensitive.
You showed me how to do it with the extension and data type citext.

How do I make the same field accent insensitive as well?
I managed to install the extension "unaccent" in Postgres 9.1.2
using create extension. Unfortunately I could only use it in the where
clauses
of sql statements. I want to make a field behave accent insensitive during
comparisons just like citext does for case insensitive.

The reason I want to do this is because it's one of the options I am used
to in
Microsoft SQL Server. I can do something like this:

select * from table1 where unaccent(field1)='John';

I can also do this:

select * from table1 where unaccent(field1)::citext='JohN';

But I want field1 to have this a default behaviour?

Thanks

H.F.

Browse pgsql-general by date

  From Date Subject
Next Message Willem Buitendyk 2012-01-19 20:03:14 Re: Cannot connect remotely to postgresql
Previous Message Lincoln Yeoh 2012-01-19 19:15:21 Re: On duplicate ignore