Question 178241 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – Redshift table joins using Similar To

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.

Tags:

2

Answers


  1. 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
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search