0

I have this query that runs in Redshift:

Select A.* 
From TableA A 
join TableB B 
on A.Col like B.Pattern

Where the Pattern column in TableB looks like:

('%foo%', '%bar%', ...)

TableB has hundreds of pattern rules and is used in several locations to its easier and cleaner to maintain one table of string rules.

Is there a way to use similar to instead of like in the join condition in Redshift? I.e. Select A.* From TableA A join TableB B on A.Col similar to B.Pattern

When I try the above query, I get this error:

Specified types or functions (one per INFO message) not supported on Redshift tables.

double-beep
  • 5,031
  • 17
  • 33
  • 41
JimmyBuffet_Express
  • 290
  • 5
  • 19

2 Answers2

0

Works fine for me:

with cte1 AS (
    select 1 AS id, 'ABC'::varchar as col
    union
    select 2 AS id, 'BDE'::varchar AS col
), cte2 AS (
    select '%BC%'::varchar AS patt
)
select a.*
from cte1 a
inner join cte2 b
    ON a.col similar to b.patt
dfundako
  • 8,022
  • 3
  • 18
  • 34
  • I tried converting your subqueries to temporary tables and the following causes the same error I originally received: { Select * Into #tmp1 From ( select 1 AS id, 'ABC'::varchar as col union select 2 AS id, 'BDE'::varchar AS col ); Select * Into #tmp2 From ( select '%BC%'::varchar AS patt ); with cte1 AS ( select * From #tmp1 ), cte2 AS ( Select * From #tmp2 ) select count(*) from cte1 a inner join cte2 b ON a.col similar to b.patt } – JimmyBuffet_Express May 12 '23 at 17:32
  • Do you have any idea why when I alter your code to use temp tables instead of sub queries it doesnt work? – JimmyBuffet_Express May 22 '23 at 15:47
-1

The problem you have is that one of your tables is on the leader node and is using leader-node only data types, and so cannot participate in a query which recruits worker nodes (which do not know about these types).

I may be wrong, but it may be you need an introduction to the basics of Redshift. Have a look at this;

https://www.redshiftresearchproject.org/white_papers/downloads/introduction_to_the_fundamentals_of_amazon_redshift.html

Max Ganz II
  • 149
  • 5