I have a query that is driving me nuts. In one table we have data that is split between two columns and I'm trying to pull in all values from another table where that column is represented by one piece of data. Also, all the info in column2 is unique but not in col 1.
table1
col1_pfx col2_number col3
Table2 col1 col3
update table1 set col3=(select col3 from table2 where table2.col1=(table1.col1_pfx || table1.col2_number));
I've tried using a join and all other methods and nothing seems to work. Table2 has a primary key on col1 yet when I do explain posgres still seems to do a sqential scan on that column.
