I use Postgresql 9.1.7 on Linux FC17 64bit, my locale is UTF8.
My full table definition is
CREATE TABLE "TEST"
(
"RECID" bigint NOT NULL DEFAULT next_id(),
"TST_PAYLOAD" character varying(255),
CONSTRAINT "PK_TEST" PRIMARY KEY ("RECID")
USING INDEX TABLESPACE local
)
WITH (
OIDS=FALSE
);
CREATE INDEX "TEST_PAYLOAD_PATTERN_1_IDX"
ON "TEST"
USING btree
("TST_PAYLOAD" COLLATE pg_catalog."default" varchar_pattern_ops)
TABLESPACE local;
CREATE INDEX "TEST_PAYLOAD_TRIGRAM_GIST_1_IDX"
ON "TEST"
USING gist
("TST_PAYLOAD" COLLATE pg_catalog."default" gist_trgm_ops)
TABLESPACE local;
CREATE INDEX "TEST_PAYLOAD_TRIGRAM_GIN_1_IDX"
ON "TEST"
USING gin
("TST_PAYLOAD" COLLATE pg_catalog."default" gin_trgm_ops)
TABLESPACE local;
The COLLATE pg_catalog."default" clause is inserted by the DB (default is "Unicode"). I also tried to define the Trigram index with COLLATE pg_catalog."C" but the behavior did not change. I did vacuum and analyze after creating each index.
The field "TST_PAYLOAD" contains 26389 names of cities, all in uppercase.
