postgresql - comparing column values in SQL

Question

I am working on multiple tables in PostgreSQL and I want to compare the column values of two tables but one of them is written differently here is how

it's the same values but one of them starts with three Zeros. I've tried this

select * from table1, table2
where table1.projectid=table2.project_id and operating_unit= 'USA'

I even tries to replace '=' with 'IN' but both return an empty table

question from:https://stackoverflow.com/questions/65891205/comparing-column-values-in-sql

Answer 1

Your code should work if one of the values is a number. But neither are. How about converting them?

select *
from table1 join
     table2
     on table1.projectid::numeric = table2.project_id::numeric and
        operating_unit = 'USA';

Sadly, having to convert on both ends precludes the use of indexes. So an alternative is to just change one side or the other:

select *
from table1 join
     table2
     on '000' || table1.projectid] = table2.project_id and
        operating_unit = 'USA';

At least this makes it possible to use an index.