PostgreSQL problem with CAST

In notifications_page_thread() function there is a query
that joins on columns that are not of the same type
resulting in a type mismatch. MySQL and PostgreSQL
up to 8.3 normally perform an automatic type cast.

With 8.3 these situations require an explicit type
cast. We've already updated the query below to
use an explicit CAST. However this was still not
working correctly in PostgreSQL 8.3 as it was not
successfully pulling the node type and node title
from the node table. (query reformatted for
readability)

$query = "
SELECT
  s.*, f.value AS nid, n.type AS node_type, n.title 
FROM 
  {notifications} s 
INNER JOIN 
  {notifications_fields} f 
ON
  s.sid = f.sid
LEFT JOIN
  {node} n
ON
  f.value = CAST(n.nid AS CHAR)
WHERE
  s.uid = %d AND
  s.type = 'thread' AND
  s.event_type = 'node' AND
  s.conditions = 1 AND
  f.field = 'nid'
ORDER BY 
  node_type, n.title";

After trying out a lot of fixes I was able to get this
working by specifying an explicit length to the CAST AS CHAR.

Since the left column f.value is a VARCHAR with max length
of 255, I set the CAST AS CHAR to 255, so the JOIN clause
becomes 'f.value = CAST(n.nid AS CHAR(255))'

Yes this is all a pain in the ass. PostgreSQL is being strict about
the typecasting for sure but it would really nice if MySQL just had
a richer set of explicit CAST functions...MySQL has no CAST AS INTEGER
instead you have to use CAST AS UNSIGNED INTEGER. MySQL also
has no CAST AS VARCHAR or AS TEXT which would have worked in
this situation as well.