Thread: Trouble with text search select statement
I am having trouble getting a multiple column text search select statement to work.
I've setup the following:
CREATE TABLE tstest (col1 text, col2 text);
INSERT INTO tstest (col1, col2) VALUES ('bird', 'moon');
CREATE INDEX tstest_ts_idx ON tstest USING gin(to_tsvector('english', coalesce(col1, '') || '' || coalesce(col2, '')));
I then execute what I believe is the correct select statement:
SELECT * FROM tstest WHERE to_tsvector('english', coalesce(col1, '') || '' || coalesce(col2, '')) @@ to_tsquery('english', 'bird');
If I remove the second column from the to_tsvector in the select statement then it returns the row.
SELECT * FROM tstest WHERE to_tsvector('english', coalesce(col1, '')) @@ to_tsquery('english', 'bird');
I know I've missed something basic but I can't figure out what.
As a side note I don't understand the purpose of the || '' || between the columns in the to_tsvector. I saw it in several examples. I've tried both with and without it with no change.
Thanks in advance.
-Mont
I've setup the following:
CREATE TABLE tstest (col1 text, col2 text);
INSERT INTO tstest (col1, col2) VALUES ('bird', 'moon');
CREATE INDEX tstest_ts_idx ON tstest USING gin(to_tsvector('english', coalesce(col1, '') || '' || coalesce(col2, '')));
I then execute what I believe is the correct select statement:
SELECT * FROM tstest WHERE to_tsvector('english', coalesce(col1, '') || '' || coalesce(col2, '')) @@ to_tsquery('english', 'bird');
If I remove the second column from the to_tsvector in the select statement then it returns the row.
SELECT * FROM tstest WHERE to_tsvector('english', coalesce(col1, '')) @@ to_tsquery('english', 'bird');
I know I've missed something basic but I can't figure out what.
As a side note I don't understand the purpose of the || '' || between the columns in the to_tsvector. I saw it in several examples. I've tried both with and without it with no change.
Thanks in advance.
-Mont
Mont Rothstein wrote: > I am having trouble getting a multiple column text search select > statement to work. > > I've setup the following: > > CREATE TABLE tstest (col1 text, col2 text); > INSERT INTO tstest (col1, col2) VALUES ('bird', 'moon'); > CREATE INDEX tstest_ts_idx ON tstest USING gin(to_tsvector('english', > coalesce(col1, '') || '' || coalesce(col2, ''))); > > I then execute what I believe is the correct select statement: > > SELECT * FROM tstest WHERE to_tsvector('english', coalesce(col1, '') > || '' || coalesce(col2, '')) @@ to_tsquery('english', 'bird'); > > If I remove the second column from the to_tsvector in the select > statement then it returns the row. > > SELECT * FROM tstest WHERE to_tsvector('english', coalesce(col1, '')) > @@ to_tsquery('english', 'bird'); > > I know I've missed something basic but I can't figure out what. > > As a side note I don't understand the purpose of the || '' || between > the columns in the to_tsvector. I saw it in several examples. I've > tried both with and without it with no change. '' isn't supposed to to be the empty string, it's supposed to be a quoted space. to_tsvector separates on whitespace. So with the space, it will have the words bird and moon.Without the space, it will have the single word birdmoon. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: kgore4@une.edu.au
Thank you. I knew it had to be something simple.
Much appreciated.
-Mont