Hi! Ie expect some problems in support of comparison operators for text, because locale string comparison can have unexpected behaviour. Let's see the example. Create table with words and add extra leading space to some of them.
test=# create table dict(id serial, word text); NOTICE: CREATE TABLE will create implicit sequence "dict_id_seq" for serial column "dict.id" CREATE TABLE test=# \copy dict(word) from '/usr/share/dict/american-english'; test=# update dict set word = ' '||word where id%2=0; UPDATE 49284 I use Ubuntu 11.04 with ru_RU.utf8 locale. So, comparison operators ignores leading spaces. test=# select * from dict where word between 'cart' and 'cary'; id | word -------+---------------- 3029 | Carter 3031 | Cartesian 3033 | Carthage's 3035 | Cartier 3037 | Cartwright 3039 | Caruso 3041 | Carver 28419 | cart 28421 | carted 28423 | cartel's 28425 | cartilage 28427 | cartilages 28429 | carting 28431 | cartographer's 28433 | cartography 28435 | carton 28437 | cartons 28439 | cartoon's 28441 | cartooning 28443 | cartoonist's 28445 | cartoons 28447 | cartridge's 28449 | carts 28451 | cartwheel's 28453 | cartwheeling 28455 | carve 28457 | carver 28459 | carvers 28461 | carving 28463 | carvings 3030 | Carter's 3032 | Carthage 3034 | Carthaginian 3036 | Cartier's 3038 | Cartwright's 3040 | Caruso's 3042 | Carver's 28420 | cart's 28422 | cartel 28424 | cartels 28426 | cartilage's 28428 | cartilaginous 28430 | cartographer 28432 | cartographers 28434 | cartography's 28436 | carton's 28438 | cartoon 28440 | cartooned 28442 | cartoonist 28444 | cartoonists 28446 | cartridge 28448 | cartridges 28450 | cartwheel 28452 | cartwheeled 28454 | cartwheels 28456 | carved 28458 | carver's 28460 | carves 28462 | carving's (59 rows) But if I create spgist index query result differs. test=# create index dict_idx on dict using spgist (word); CREATE INDEX test=# select * from dict where word between 'cart' and 'cary'; id | word -------+---------------- 28419 | cart 28421 | carted 28423 | cartel's 28425 | cartilage 28427 | cartilages 28429 | carting 28431 | cartographer's 28433 | cartography 28435 | carton 28437 | cartons 28439 | cartoon's 28441 | cartooning 28443 | cartoonist's 28445 | cartoons 28447 | cartridge's 28449 | carts 28451 | cartwheel's 28453 | cartwheeling 28455 | carve 28457 | carver 28459 | carvers 28461 | carving 28463 | carvings (23 rows) ------ With best regards, Alexander Korotkov.
