Обсуждение: : :Full text search query ::

Hi,

On 25.2.2015 12:50, JD wrote:
> Hi All,
>
> please find herewith the following  query
>
> 1. select * from partdetails where scode=118 and
> (to_tsvector('english'::regconfig, part_number::text)) @@ to_tsquery('104')
>
> it is showing only 1 record as output, it is expected to give 17 records
> as output.
>
> 2. select * from partdetails where scode=118 and
> (to_tsvector('english'::regconfig, part_number::text)) @@ to_tsquery('104/')
>
> it is showing only 1 record as output, it is expected to give 17 records
> as output.
>
> 3. select * from partdetails where scode=118 and
> (to_tsvector('english'::regconfig, part_number::text)) @@
> to_tsquery('104/1')
>
> it is showing  17 records as output.
>
> In our search case we are passing parameter value as 104 and expected to
> get 17 records.
>
>
> Kindly some one guide here.

You need to post 'to_tsvector('english', part_number)' for the 16 rows
that you think should be returned but aren't.

Fulltext works so that it transforms the source (part_number in this
case) as defined in the text search configuration ('english'), and
compares this with the tsquery.

My bet is that the transformation keeps the whole string ('104/1')  in
this case, so that it does not match the tsquery.

ISTM you're trying to do a prefix search on the part_number. In that
case fulltext may not be the right solution, because it's fuzzy by
nature. If you have two-part part numbers (i.e. it's always A/B) then
maybe split that into two fields, and use simple equality conditions on
each field.

So instead of column 'part_number' containing valuye '104/1' use two
columns part_number_a and part_number_b, containing values '104' and
'1', and simple equality queries

    WHERE part_number_a = '104' and part_number_b = '1'

or (if you want to match just the first part)

   WHERE part_number_a = '104'

Another option is to use an index with a 'varchar_pattern_ops' opclass,
which allows you to do prefix LIKE queries [1]

   CREATE INDEX custom_partnum_idx
             ON partdetails (part_number varchar_pattern_ops);


   SELECT ... FROM partdetails WHERE part_number LIKE '104/%'


[1] http://www.postgresql.org/docs/9.2/static/indexes-opclass.html

regards

--
Tomas Vondra                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services