I need an advice with searching of record based on specified string.

Search strings can contain values from these columns. Values in this string don´t have to be strictly identical given in the correct order and also the values of some columns in this string may be missing.

Example of search string:

22 Karntner Wien

And I get for example result with top 5 similar records.

I think I should use full text search, but I have no experiences with it. Can you tell me how to proceed?

2 Answers 2


I suggest this expression for query and index:

WHERE  to_tsvector('simple', f_concat_ws(' ', country, city, street, house_nr, postcode))
    @@ plainto_tsquery('simple', '22 Kärntner Wien');

Note the custom function f_concat_ws() above. concat_ws() is only STABLE, not IMMUTABLE. Create this function first:

  RETURNS text
'SELECT array_to_string($2, $1)';

It can be used as drop-in replacement for concat_ws(), except that it only accepts actual text data as input (which allows us to make it IMMUTABLE without cheating, effectively). Detailed explanation (read it!):


Or, if you you have superuser privileges, see:

For many columns, this is shorter and faster. You could do without it but then the syntax gets rather verbose (see joanolo's answer).

The matching index to go with this:

CREATE INDEX tbl_adr_fts_idx ON tbl USING GIN (
       to_tsvector('simple', f_concat_ws(' ', country, city, street, house_nr, postcode)));

You are dealing with international address data, so do not use the english text search configuration. Stemming makes little sense for names and most of your example data is not even English to begin with. Use the simple configuration instead. You need the form with two parameters - see below.

Concatenate the strings and call the more expensive function to_tsvector() once. Use concat_ws() to deal with possible NULL values elegantly. Cheaper overall, and also shorter.

Like I commented, Full Text Search has limited support for fuzzy matching, but there is the often overlooked feature of prefix matching:

So, if you are not sure whether it's 'Kärntner' or 'Kärnten', and whether it's 'Straße', 'strasse' or 'Strabe' (like in your buggy example data) but you know that the second word follows the first, you could:

... @@ to_tsquery('simple', '22 & Kärnt:* <-> Stra:* & Wien')

<-> is the phrase search operator and requires Postgres 9.6.

And if you want to ignore diacritical signs as well ('ä' <> 'a'), add unaccent() to the mix. You can use it as separate function or you can add it as dictionary to your text search configuration. You need to install the extension first ...

Overview over pattern matching option in typical Postgres installations:

Joanolo already provided some basic information about FTS and the link to the manual for more.

Adressing your comment

I am trying add this index it but gives me an error:

ERROR: functions in index expression must be marked IMMUTABLE

There are two variants of the function to_tsvector() - see "function overloading". The 1st takes only text, the 2nd takes regconfig and text. See for yourself:

SELECT proname, provolatile, proargtypes[0]::regtype, proargtypes[1]::regtype
FROM   pg_proc
WHERE  proname = 'to_tsvector';

Only the second is IMMUTABLE and can be used in an index expression directly. 'simple' in the above example is a text search configuration (regconfig).

More importantly, my oversight: concat_ws() (which I had in my first version) is only STABLE, not IMMUTABLE. I added necessary steps above.


  • I am trying add this index but give me error "ERROR: functions in index expression must be marked IMMUTABLE" Feb 12, 2017 at 17:17
  • I check “accent insensitive” so here is my select which I wrote : pastebin.com/y2ad1upn ... Do you think is it ok? It works but this query runtime is about 800ms withou index. I've tried overload this function to_tsvector and use custom, but stll same issues. Can you give me example of index for my current select with accent insensitive? I'm sorry but I am totaly new in postgres. Thanks Feb 12, 2017 at 19:07
  • @DenisStephanov: I had an oversight in my solution (sorry for the confusion!): concat_ws() is not IMMUTABLE. I added a solution and explanation above. Feb 12, 2017 at 19:41
  • 1
    I use unaccent function in select in f_concat_ws() function and it works ! :) thanks for help :) Feb 12, 2017 at 20:08
  • 1
    in my case @ErwinBrandstetter it just fits right, relatively big table (10GB) that get updated monthly by cron job, it is just perfect. Thanks again.
    – lauksas
    Oct 2 at 23:20

Let's imagine this is your table and some data:

    country text,
    city text,
    street text,
    house_number text,
    post_code text
) ;

   ('Österreich', 'Vienna', 'HauptStrasse', '123', '12345'),   
   ('France', 'Paris', 'Rue du Midi', '12A', '01234'),   
   ('España', 'Barcelona', 'Passeig de Gràcia', '32', '08001'),   
   ('United Kingdom', 'London', 'Oxford Street', '20', 'W1D 1AS'),
   ('Nederland', 'Amsterdam', 'Leidsekruisstraat', '6-8', '1017 RH') ;

[NOTE: check it at http://rextester.com/DOJN8533]

The way to perform a full text search to several columns using PostgreSQL (assuming 'english' is the name of your FTS configuration), is by using a query like:

        to_tsvector('english', coalesce(country, ''))      || 
        to_tsvector('english', coalesce(city, ''))         || 
        to_tsvector('english', coalesce(street, ''))       || 
        to_tsvector('english', coalesce(house_number, '')) ||
        to_tsvector('english', coalesce(post_code, '')) 
    ) @@ plainto_tsquery('english', 'Amsterdam') ;

The where clasuse means:

 (this tsvector = document) @@ /* matches */  (this tsquery = query)

A tsvector is a special data type used by PostgreSQL to store transformed data (for instance, all lowercased; with commas taken out, with words identified and listed, etc.) about a text. A tsquery is a way to ask for characteristics of a document (for instance containing this _and_ that).

The || operator combines tsvectors (let's say it "adds them together").

If you want to speed up things, you should have one functional index, defined like:

    ON t USING gist ( 
        to_tsvector('english', coalesce(country, '')) || 
        to_tsvector('english', coalesce(city, '')) || 
        to_tsvector('english', coalesce(street, '')) || 
        to_tsvector('english', coalesce(house_number, '')) ||
        to_tsvector('english', coalesce(post_code, ''))
) ;

You need to carefully check the documentation about Full Text Search. It is a bit intimidating, because there are lots of possibilities, but it's worth spending the time.

To sort out results when there are many, you should use he ts_rank function to ORDER BY and then limit.

  • I have one more question .. is possible add to this fuzzy match? For example if I enter "Kyje", it will find Kyjev, Arkyjevlod, Kyjstron, and so on. I try this example what you wrote, but it works only when I enter correct word which is in one of these columns Feb 11, 2017 at 21:40
  • AFAIK full-text search cannot be used for fuzzy-search, although you can use different configurations (dictionaries) to have stemming (i.e. quick and quickly will be considered equivalent) and synonyms. If you want to look for similarity you can use trigram indices and trigram similarity. Look for pg_trgm
    – joanolo
    Feb 11, 2017 at 22:26
  • @DenisStephanov: FTS does support prefix matching, though. I added an answer. Feb 12, 2017 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.