Geek Answers Handbook

Has anyone had success using a specific locale for a PostgreSQL database so that text comparisons are not case sensitive?

I am developing an application in Rails on OS X using PostgreSQL 8.4. I need to configure the database for the application so that standard text queries are case insensitive. For example:

SELECT * FROM documents WHERE title = 'incredible document'

should return the same result as:

SELECT * FROM documents WHERE title = 'Incredible document'

To be clear, I do not want to use:

(1) LIKE in a where clause or any other type of special comparison operator

(2) citext for a column data type or any other special column index

(3) any type of full-text software such as Sphinx

, . Mac OS X (10.5 Leopard) "LATIN1", Collation Ctype "en_US.ISO8859-1". .

.

!

, , . , . , , , . , name name_lower. , -. , PostgreSQL , SQL Server (, DOCI).

, .

+5
5

, , - , . - :

SELECT * FROM documents WHERE upper(title) = upper('incredible document')

, , , , , , .

CREATE INDEX I1 on documents (upper(title))
+1

, , , , - = . , , . , citext; ORM, SQL.

( , , - .)

+1

" , , ".

.

0

, . , , . , , .

ilike, , , , ORM ActiveRecord, .

- postgres: http://archives.postgresql.org/pgsql-php/2003-05/msg00045.php

edit: .

0
SELECT * FROM documents WHERE title ~* 'incredible document'
0

More articles:


All Articles