Hello
I'm currently trying to implement some kind of home grown full text
search. Basic idea as in fulltextindex examples with postgresql-6.4.2,
but everything is done on client side. (It doesn't matter actually)
My tables layout:
create table faqs (
id int PRIMARY KEY DEFAULT nextval('s_faqs_id'),
product_id int NOT NULL, // reference to table products
category_id int , // reference to table categories
source_id int NOT NULL, // reference to table faq_sources
cleared bool NOT NULL DEFAULT 'f', // if entry is cleared
accepted bool NOT NULL DEFAULT 'f', // if entry is accepted by
rating int NOT NULL DEFAULT 0,
mod_time datetime DEFAULT TEXT 'now', // date of last modification
faq_version int NOT NULL DEFAULT 0, // version of entry
);
create table faq_keywords (
faq_id int,
keyword char(32),
times_found int,
category int
);
create unique index pk_faq_keywords on faq_keywords(keyword, faq_id,
category);
Select statement which causes error mentioned in header:
select distinct f.id from faqs f, faq_keywords fk1, faq_keywords fk2,
faq_keywords fk3 where cleared = '0' and accepted = '0' and fk1.keyword =
'error' and fk2.keyword = 'manual' and fk1.faq_id = fk2.faq_id and
fk3.keyword = 'user' and fk2.faq_id = fk3.faq_id and f.id=fk3.faq_id;
If I drop pk_faq_keywords, then create index only on 'keyword' field of
table 'faq_keywords' everything works fine. Everything also works fine if
I try to to search only on 2 keywords.
Another funny thing is: if you add 'order by f.mod_time' to the end of
this statement (then it works ) it will ignore 'distinct' keyword.
Sincerely, Sergei
