1

I am going to use a for/each loop, to search different names (table1) among textual information of records in another table (table2) using regular expressions.

SELECT id FROM "table1"
where tags ~* 'south\s?\*?africa'
   or description ~* 'south\s?\*?south'
order by id asc;

but I do not know how to put it in a for each loop!

table1:

 t1ID | NAME
 1    | Shiraz      
 2    | south africa
 3    | Limmatplatz

table2:

t2ID |TAGS                   | DESCRIPTIONS
101  |shiraz;Zurich;river    | It is too hot in Shiraz and Limmatplatz
201  |southafrica;limmatplatz| we went for swimming

I have a list of names in table1. Another table has some text information that might contain those names. I would like to get back the id of table2 that contains items in table1 with the id of the items.

For example:

t2id | t1id
101  |1
101  |3
201  |2
201  |3

My tables have 60,000 and 550.000 rows. I need to use a way that time wise be efficient!

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
GeoBeez
  • 920
  • 2
  • 12
  • 20

1 Answers1

1

You don't need a loop. A simple join works.

SELECT t2.id AS t2id, t1.id AS t1id
FROM   table1 t1
JOIN   table1 t2 ON t2.tags        ~* replace(t1.name, ' ', '\s?\*?')
                 OR t2.description ~* replace(t1.name, ' ', '\s?\*?')
ORDER  BY t2.id;

But performance will be terrible for big tables.
There are several things you can do to improve it:

  1. Normalize table2.tags into a separate 1:n table.
    Or an n:m relationship to a tag table if tags are used repeatedly (typical case). Details:
  2. Use trigram or textsearch indexes
  3. Use a LATERAL join to actually use those indexes.
  4. Ideally, use the new capability in Postgres 9.6 to search for phrases with full text search. The release notes:

Full-text search can now search for phrases (multiple adjacent words)

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228