How to do text search in postgresql with multiple results

Question

Let's say I get the following table when I do select name, alternative_name from persons;

          name            |           alternative_name
--------------------------+----------------------------------
 Johnny A                 | John the first
 Johnny B                 | The second John

Now with this query select name from persons where to_tsvector(name || alternative_name) @@ to_tsquery('John');:

          name            |           alternative_name
--------------------------+----------------------------------
 Johnny A                 | John the first

Shouldn't I get both? How can I do a full text search on both the name and columns where I get all rows that match the search query?

Edit: Yes, there is indeed a typo here. It is to_tsquery

Don't you want to use `to_tsquery` instead of `to_query` ? And you really should add a space so that you don't miss words because of concatenation. — Denys Séguret, Feb 02 '18 at 14:44
Possible duplicate of [Postgres full text search: how to search multiple words in multiple fields?](https://stackoverflow.com/questions/30662755/postgres-full-text-search-how-to-search-multiple-words-in-multiple-fields) — Denys Séguret, Feb 02 '18 at 14:50

score 1 · Accepted Answer · answered Feb 02 '18 at 14:45

1

you concat without space:

t=# with c(n,a) as (values('Johnny A','John the first'),('Johny B','The second John'))
select * from c
where to_tsvector(n || a) @@ to_tsquery('John')
;
    n    |        a
---------+-----------------
 Johny B | The second John
(1 row)

so first haystack becomes Johnny AJohn the first, thus lexeme do not match, try:

t=# with c(n,a) as (values('Johnny A','John the first'),('Johny B','The second John'))
select * from c
where to_tsvector(n ||' '|| a) @@ to_tsquery('John')
;
    n     |        a
----------+-----------------
 Johnny A | John the first
 Johny B  | The second John
(2 rows)

answered Feb 02 '18 at 14:45

Vao Tsun

47,234
13
100
132

I mentioned that in comment but that's obviously not the problem according to the results OP is displaying (assuming that's the real data, query and results). – Denys Séguret Feb 02 '18 at 14:47
@DenysSéguret yes - he has the "opposite result" - but I assumed his code is not copy paste after seeing `to_query`. Fair point though - I should have make clear it before answering. – Vao Tsun Feb 02 '18 at 16:30
@DenysSéguret : and you are correct in assuming that this is not real data. – Arnab Datta Feb 02 '18 at 17:03

How to do text search in postgresql with multiple results

1 Answers1