Postgres: How to join 2 tables on column with different datatypes?

Question

I have 2 Tables and want to join them in a view.

LIN_MD_Functions

bigint       text

function_id  function_name
1            Consulting
2            Education

LIN_Main_Followers

char var        text         text         text    
     
metric_type_id  metric_type  metric_value metric_name
1               function     56           followers 
2               function     62           likes
us              country      79           views

...and many other types and values

I am trying to join them like ( metric_type_id = function_id AND metric_type = 'function'). The thing is that metric_type_id column contains numbers and char values and I cannot just turn it to int.

But it throws an error cos of different column types

The code

 SELECT lin.metric_name,
    lin.metric_type,
    lin.metric_value,
    func.function_name,
        CASE
            WHEN lin.metric_type_id::text ~ '^\d+$'::text THEN lin.metric_type_id::integer
            ELSE lin.metric_type_id
        END AS "id_m" 
   FROM "LIN_Main_Followers" lin
     LEFT JOIN "LIN_MD_Functions" func ON (lin.metric_type_id = func.function_id AND
                                           lin.metric_type = 'function'
                                          )

I understand that in JOIN I have to somehow convert the id column again, but cannot get how. I CANNOT change datatype in functions table.

My ideal result is:

VIEW

metric_name metric_type metric_value  function_name
followers   function    56            Consulting
likes       function    62            Education

Tim Biegeleisen · Accepted Answer · 2021-09-20 07:49:10Z

1

Given that metric_type_id contains mixed text/numbers, you should therefore treat is as text and cast the function_id column to text:

SELECT
    lin.metric_name,
    lin.metric_type,
    lin.metric_value,
    func.function_name,
    CASE WHEN lin.metric_type_id::text ~ '^\d+$'::text
         THEN lin.metric_type_id::integer
         ELSE lin.metric_type_id END AS "id_m" 
FROM LIN_Main_Followers lin
LEFT JOIN LIN_MD_Functions func
    ON lin.metric_type_id = func.function_id::text AND
       lin.metric_type = 'function';

answered Sep 20, 2021 at 7:49

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres: How to join 2 tables on column with different datatypes?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related