I have a postgresql 9.1 database table, "en_US.UTF-8":
CREATE TABLE branch_language
(
id serial NOT NULL,
name_language character varying(128) NOT NULL,
branch_id integer NOT NULL,
language_id integer NOT NULL,
....
)
The attribute name_language contains names in various languages. The language is specified by the foreign key language_id.
I have created a few indexes:
/* us english */
CREATE INDEX idx_branch_language_2
ON branch_language
USING btree
(name_language COLLATE pg_catalog."en_US" );
/* catalan */
CREATE INDEX idx_branch_language_5
ON branch_language
USING btree
(name_language COLLATE pg_catalog."ca_ES" );
/* portuguese */
CREATE INDEX idx_branch_language_6
ON branch_language
USING btree
(name_language COLLATE pg_catalog."pt_PT" );
Now when I do a select I am not getting the results I am expecting.
select name_language from branch_language
where language_id=42 -- id of catalan language
order by name_language collate "ca_ES" -- use ca_ES collation
This generates a list of names but not in the order I expected:
Aficions i Joguines
Agència de viatges
Aliments i Subministraments
Aparells elèctrics i il luminació
Art i Antiguitats
Articles de la llar
Bars i Restaurants
...
Tabac
Àudio, Vídeo, CD i DVD
Òptica
As I expected the last two entries to appear in different positions in the list.
Creating the indexes works. I don't think they are really necessary unless you want to optimize for performance.
The select statement however seems to ignore the part: collate "ca_ES".
This problem also exists when I select other collations. I have tried "es_ES" and "pt_PT" but the results are similar.