Scott Newton <scott.newton@vadacom.co.nz> writes:
> I have the following rather complicated SQL which works under MySQL but fails
> under PostgreSQL 8.3. The SQL is generated by A2Billing
> (http://www.asterisk2billing.org/cgi-bin/trac.cgi). The issue is the following
> part of the SQL:
> as tf on tf.dnid=substr(cdr.dnid,1,length(tf.dnid))
> where tf.dnid is a bigint and cdr.dnid is varchar(40).
[ blanch... ] That's not the worst SQL code I've ever seen, but it
might be in the top ten. They're apparently trying to see whether
tf.dnid, taken as a string, matches a prefix of cdr.dnid --- but what if
you have say 123 versus "1234foo"? This will match, but most likely
it shouldn't. They need to acquire a clue, and a better data
representation.
Anyway, you've got two different typing violations there, so you need
two casts to fix it:
as tf on tf.dnid::text = substr(cdr.dnid,1,length(tf.dnid::text))
(salt to taste with CAST and/or varchar if you prefer, but it's
all the same)
regards, tom lane