On Tue, 16 Sep 2008, Glyn Astill wrote:
Hi chaps,
I was just wondering if there's any way to tweak the way an an index is ordered on a text field?
Yes, it's called functional index. Write your function, which does
whatever you want with your data and create index (foo(artist))
I have a table with a varchar field "artist" (see table def below), and if I order by the field "artist" it does not order as expected when there are characters such as ^ at the start of the text. For example, the query "select artist from artist order by artist;" orders as follows:
BILLY BRAGG
BILLY COBHAM & ASERE
^ BILLY ELLIOT 40 OFFER
^ BILLY ELLIOT HARD ROCK CAFE MEAL OFFER
BILLY ELLIOT THE MUSICAL
BILLY OCEAN
BIOHAZARD
I'd expect the rows starting with the caret to appear either at the start or end of, rather than in the middle, it appears as if the index ignores them.
Database locale is Latin1
Am I missing something obvious here?
Thanks
Glyn
CREATE TABLE events.artist
(
recnum bigint NOT NULL DEFAULT nextval(('"events"."artist_dfseq"'::text)::regclass),
artist character varying(50) NOT NULL DEFAULT ' '::character varying,
price_list character varying(4) NOT NULL DEFAULT ' '::character varying,
CONSTRAINT artist_index01 PRIMARY KEY (artist)
)
ALTER TABLE events.artist
ADD CONSTRAINT artist_index01 PRIMARY KEY(artist);
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
