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
– Denys Séguret, Commented Feb 2, 2018 at 14:44
Possible duplicate of Postgres full text search: how to search multiple words in multiple fields? — Denys Séguret
– Denys Séguret, Commented Feb 2, 2018 at 14:50

Vao Tsun · Accepted Answer · 2018-02-02 14:45:17Z

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 2, 2018 at 14:45

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Denys Séguret Over a year ago

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).

Vao Tsun Over a year ago

@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.

Arnab Datta Over a year ago

@DenysSéguret : and you are correct in assuming that this is not real data.

Collectives™ on Stack Overflow

How to do text search in postgresql with multiple results

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related