Re: MySQL -> PostgreSQL conversion issue

Scott Newton <scott(dot)newton(at)vadacom(dot)co(dot)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