Identifying which column matches a full text search

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

Responses

Browse pgsql-sql by date

  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 (