Thread: : :Full text search query ::
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.
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