Implementing search on but multiple psql fields?

Question

PostgreSQL

There is a table of addresses of this kind

If the user enters an address of this type "Blelarus, Minsk, Sukharevskaya st., 79, 3" into the input
How to find the most exact match for this string and what form is it better to bring the data to transfer to sql query?

Answer 1

Well, if the table is filled in as you have shown, and the query is the same as you have shown, then split by commas and WHERE ... ILIKE ... AND .... ILIKE ...
But I still advise you to first look for an analogue FIAS in the Republic of Belarus.
Because addresses are a very strange and mysterious thing.
I don’t know how it is in Belarus, but we have such things:
the city of Moscow, the city of Zelenograd, a microdistrict .... (yeah, Zelenograd is not in the Moscow region, but in Moscow)
"So-and-so region, Syakoy-and-so village, house 2 "without a street, but in the same village there may be a street. Lenina, house 2 - and this is a different house.
About buildings \ letters \ buildings - you can write a separate saga, entirely from obscene expressions, because, for example, the house in which I am now, in some STATE resources is designated as 4/1, in others 4 building 1.
There were several articles on Habré how people screwed fias to postgres. Look.

Answer 2

IMHO you can do something like lookup.
Those. enter the first characters of the city, he is prompted by all the cities starting with these characters, then the streets, etc.
And so parse the input line into parts, determine from these parts what it is - a country, a city, a street. Well, then create a request.
You can also look towards Elastic Seacrh