Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

lower(something)

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?

share|improve this question
    
How are you going to use the 'map' value, just to find equivalent rows or to actually map rows onto a 'primary/parent' row? –  Jack Douglas Jan 14 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'. –  Tiago Stapenhorst Martins Jan 15 at 1:09
add comment

3 Answers

up vote 3 down vote accepted

Database design

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

CREATE TABLE map (
  map_id int PRIMARY KEY
 ,map    text UNIQUE NOT NULL
);

-- And that's how I would shape your original table:
CREATE TABLE word (
  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.

[^[:alnum:]]
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'))$$
LANGUAGE sql IMMUTABLE;

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
share|improve this answer
add comment

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')); $$
LANGUAGE sql;

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.

share|improve this answer
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 at 16:12
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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