I'm working on PostgreSQL and I have a table like this called words_table:
Table table

I need all equivalent words (like software_design and software-design) to have its map value = to its word's equivalent id_word. In other words, I want all the words softwaredesign, software_design, software.design... and so on having the same number on its map column (in this specific case, a number from 1 to 10). The same applies to civil_engineering and IndustrialDesign.

I know this involves some regular expressions and case insensitive comparisons but i'm stuck at the SQL logic. I know these expressions could be useful:

regexp_replace(word, '(\.|:|,|&|-)','','g')

To handle the separators


To handle the uppercase lowercase matching or

UPDATE words_table SET a.map = b.id_word WHERE word ILIKE something

... WHERE word ~* something

for case insensitive matching.

Should I create new columns with regexp_replaced words and do the mapping after that with some joins? Or maybe something with CamelCase and underscore matching? Maybe functions? Which is the optimal solution?

  • How are you going to use the 'map' value, just to find equivalent rows or to actually map rows onto a 'primary/parent' row? Jan 14, 2014 at 12:08
  • @JackDouglas Yep, that's a further step on my work but it's a good question. I need a good primary/parent row, and by good I mean a distinguishable bigram (indstrialdesign, softwaredesign, civilengineering are the worst cases for me because they can't be easily split). I want to split each 'map' word value in bi-grams. I need one column with 'software' and another with 'design'. Jan 15, 2014 at 1:09

3 Answers 3


Database design

Either you already have the look-up table and forgot to mention it, or you should create one.

  map_id int PRIMARY KEY
 ,map    text UNIQUE NOT NULL

-- And that's how I would shape your original table:
  word_id serial PRIMARY KEY
 ,map_id  int REFERENCES map(map_id)
 ,word    text

Here and here is why I normally prefer text over varchar(n).
Here is why I put the two integer columns first in table word.

INSERT INTO map(map_id, map) VALUES
  (1, softwaredesign)
 ,(2, civilengineering)
 ,(3, industrialdesign)
 , ... ;

Map data

Define a function like @Daniel already suggested.

Depending on your exact requirements, I would use pre-defined character classes where possible in a regular expression instead of rolling my own. Postgres uses locale information from your OS to identify characters, digits etc.

[:alnum:] includes all numbers and digits, depending on your locale. Unlike [a-zA-Z0-9] this also identifies ä or é as characters - depending on your locale.

is the negation, i.e. all other characters.
There is also the similar class shorthand \W, but that would include the underscore _.

CREATE FUNCTION map_word(text) RETURNS text AS
$$SELECT lower(regexp_replace($1, '[^[:alnum:]]', '', 'g'))$$

This update would be most efficient then:

UPDATE word w
SET    map_id = m.map_id
FROM   map m
WHERE  m.map = map_word(w.word)
AND    w.map_id IS DISTINCT FROM m.map_id -- avoid empty updates

You may break it down into two smaller problems.

First create a function that does the word's simplification. This version just remove any non alpha-numeric character and sets the result in lower-case. Refine it if necessary.

CREATE FUNCTION simplify(text) RETURNS text AS
$$ SELECT lower(regexp_replace($1, '[^a-zA-Z0-9]', '', 'g')); $$

Next, update the map column by joining the table on itself, matching every word with its "simplified" version.

 UPDATE words_table w 
    set map=w2.id_word
 FROM words_table w2
     WHERE w2.word=simplify(w.word);

This assumes than any simplified version of a word is already part of the table, as seems to be the case in your sample data. Otherwise they should be inserted.

  • 1
    +1. An alternative is to use your simplify() function in an index, and use it in the WHERE clause, thus avoiding the UPDATE. Might be a win, it might not.
    – bma
    Jan 14, 2014 at 16:12

On the small data sets you can use functions that calculates derivatives each time you need them, but on huge joins that function will be invoked for every row again and again. That can slowdown overall performance.

More efficient is to store precalculated derivatives. One way is to store them along with the original strings but you'll get a low cardinality field that needs to be indexed. The other approach is to maintain intermediate table of derivatives used in joins to glue other tables.

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.