Answer the question
In order to leave comments, you need to log in
How to find all similar records in a table?
Hello! Please help me figure it out.
The PostgreSQL database has a table with a list of questions and the table looks like this:
| QUESTION_ID | QUESTION_TEXT |
|-------------|--------------------------------------------------|
| 1 | What is your favorite movie, cartoon and series? |
| 2 | What is your favorite movie cartoon and series |
| 3 | what is your favorite Movie, Cartoon and Series |
| 4 | Do you like apple? |
| 5 | do you like Apple |
| 6 | What is your favorite city? |
select
*
from
questions
where
question_text in (
'What is your favorite movie, cartoon and series?',
'Do you like apple?'
)
CREATE EXTENSION pg_trgm;
CREATE INDEX questions_trgm_idx ON questions
USING gin (question_text gin_trgm_ops);
select
question_text,
similarity(
question_text,
'What is your favorite movie, cartoon and series?'
)
from
answers
where
question_text % 'What is your favorite movie, cartoon and series?'
and similarity(
question_text,
'What is your favorite movie, cartoon and series?'
) >= 0.9;
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question