PostgreSQL: Set-retuning functions are not allowed in JOIN conditions

Question

I have two tables that I need to join. One table (call it table A) contains the letter code of a product in a field called "ProductCode". The other table (table B) contains the same letter code with a bunch of numbers attached to it, so I am extracting it using REGEXP_MATCHES(B.ProductCode,'([A-Za-z])','g'))[1]. Then I try to join the two tables like so:

select * from A
inner join B on A.ProductCode = REGEXP_MATCHES(B.ProductCode,'([A-Za-z])','g'))[1]

So for example:

Table A:
----------------------
Product Code | Sales
----------------------
A            | 100
B            | 200


  Table B:
---------------------
Product Code | Region
---------------------
A234         | Midwest
B543         | Southwest

The desired result of the join above would be:

------------------------------
Product Code | Sales | Region
----------------------------
A            | 100   | Midwest
B            | 200   | Southwest 

But I am getting the error: 'Set-returning functions are not allowed in JOIN conditions'.

I get it that REGEXP_MATCHES returns an array, but I am extracting an element of that array ([1]), so it should no longer be a set? Not sure how to fix it. Thanks for any suggestions.

Answer 1

For this sample data, a regexp seems overkill. You could just use substring(), or like:

select a.*, b.region
from a
inner join b on b.product_code like a.product_code || '%'