title: Issue with Postgres not recognizing CAST on join
body: <p>I'm trying to join two tables together based on an ID column. The join is not working successfully because I cannot join a <code>varchar</code> column on an <code>integer</code> column, despite using <code>cast()</code>.</p>
  
  <p>In the first table, the ID column is character varying, in the format of: <code>XYZA-123456</code>. 
  In the second table, the ID column is simply the number: <code>123456</code>.</p>
  
  <pre><code>-- TABLE 1
  create table fake_receivers(id varchar(11));
  
  insert into fake_receivers(id) values 
  ('VR2W-110528'),
  ('VR2W-113640'),
  ('VR4W-113640'),
  ('VR4W-110528'),
  ('VR2W-110154'),
  ('VMT2-127942'),
  ('VR2W-113640'),
  ('V16X-110528'),
  ('VR2W-110154'),
  ('VR2W-110528');
  
  -- TABLE 2
  create table fake_stations(receiver_sn integer, station varchar);
  
  insert into fake_stations values 
  ('110528', 'Baff01-01'),
  ('113640', 'Baff02-02'),
  ('110154', 'Baff03-01'),
  ('127942', 'Baff05-01');
  </code></pre>
  
  <p>My solution is to split the string at the dash, take the number after the dash, and cast it as an integer, so that I may perform the join: </p>
  
  <pre><code>select cast(split_part(id, '-', 2) as integer) from fake_receivers; -- this works fine, seemingly selects integers
  </code></pre>
  
  <p>However, when I actually attempt to perform the join, I'm getting the following error, despite using an explicit cast: </p>
  
  <pre><code>select cast(split_part(id, '-', 2) as integer), station
          from fake_receivers 
          inner join fake_locations 
          on split_part = fake_locations.receiver_sn -- not recognizing split_part cast as integer! 
  
  &gt;ERROR: operator does not exist: character varying = integer
  &gt;Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
  </code></pre>
  
  <p>Strangely enough, I can perform this join with my full dataset (a queried result set shows up) but I then can't manipulate it at all (e.g. sorting, filtering it) - I get an error saying <code>ERROR: invalid input syntax for integer: "UWM"</code>. The string "UWM" appears nowhere in my dataset or in my code, but I strongly suspect it has to do with the split_part cast from <code>varchar</code> to <code>integer</code> going wrong somewhere. </p>
  
  <pre><code>-- Misc. info
  select version();
  &gt;PostgreSQL 10.5 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM version 9.0.0 (clang-900.0.39.2), 64-bit
  </code></pre>
  
  <p><strong>EDIT</strong>: <a href="https://dbfiddle.uk/?rdbms=postgres_10&amp;fiddle=4ce93d4fd7a786f0e1842a2776805665" rel="nofollow noreferrer">dbfiddle exhibiting behavior</a></p>