From: | Ryan Wallace <rywall(at)interchange(dot)ubc(dot)ca> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Identifying which column matches a full text search |
Date: | 2008-07-29 18:31:48 |
Message-ID: | 00c301c8f1a9$5e9b4420$1bd1cc60$@ubc.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi all,
The following example is given in the Postgres 8.3 manual regarding how to
create a single ts_vector column for two existing columns:
ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;
UPDATE pgweb SET textsearchable_index_col =
to_tsvector('english', coalesce(title,'') || coalesce(body,''));
Then we create a GIN index to speed up the search:
CREATE INDEX textsearch_idx ON pgweb USING gin(textsearchable_index_col);
Now we are ready to perform a fast full text search:
SELECT title
FROM pgweb
WHERE textsearchable_index_col @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC LIMIT 10;
Using this approach. Is there any way of retrieving which of the original
two columns the match was found in?
Any help would be much appreciated,
Ryan
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Neyman | 2008-07-29 19:14:12 | Re: column names with - and ( |
Previous Message | maria s | 2008-07-29 16:39:30 | Re: column names with - and ( |