Skip site navigation (1) Skip section navigation (2)

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$ (view raw, whole thread or download thread mbox)
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,



pgsql-sql by date

Next:From: Igor NeymanDate: 2008-07-29 19:14:12
Subject: Re: column names with - and (
Previous:From: maria sDate: 2008-07-29 16:39:30
Subject: Re: column names with - and (

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group