Duplicate the column with an upper or lowercase version and run all queries against that. CREATE TABLE foo ( id serial PRIMARY KEY, val text, val_lower text ); Index val_lower. Use triggers to keep val and val_lower in sync and discard all attempts to write directly to val_lower. Then all queries would be of the form SELECT id, val FROM foo WHERE val_lower LIKE 'ab%'; Wouldn't want to write every table like this, but if (a) query speed trumps all other requirements and (b) functional index, CITEXT, etc. have all been rejected as not fast enough… --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu It is a good idea to have a duplicate column and index and use that column. But, we have heavy inserts/updates on this table. I am afraid that it would slow down the insert performance. But, I would definately like to test this option. Isn't it better to convert Postgres DB to case insensitive ? How difficult is that ? I want the DB to support UTF8 and be case insensitive like SQL Server. Thanks |
