Concatenated index in postgresql

Question

So basically I'm matching addresses by matching strings within 2 tables

Table B has 5m rows so I really don't want to create new columns for it every time I want to match the addresses

So I thought about creating indexes instead, my current index to match addresses would look like:

CREATE INDEX matchingcol_idx  ON tableB USING btree (sub_building_name || ', ' || building_name )

However this does not work, it doesn't accept the concatenation bar

My update query would then equal = b.sub_building_name || ', ' || b.building_name

Without a new column and an index this would take multiple hours

Is there a way to achieve this without creating new concatenation columns?

Answer 1

For an expression based index, you need to put the expression between parentheses:

CREATE INDEX matchingcol_idx  
   ON tableB USING btree ( (sub_building_name || ', ' || building_name) );

But that index will only be used if you use exactly the same condition in your where clause. Any condition only referencing one of the columns will not use that index.