Re: MySQL -> PostgreSQL conversion issue

On Thu, Oct 28, 2010 at 6:44 PM, Scott Newton
<scott(dot)newton(at)vadacom(dot)co(dot)nz> wrote:
> Hi
>
> 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). The error returned is
> ERROR:  function length(bigint) does not exist at character 2521
> 2010-10-29 13:34:27 NZDT HINT:  No function matches the given name and
> argument types. You might need to add explicit type casts.

This is usually a precedence thing, i.e. you're trying to join to a
set that doesn't exist yet to that part of the query. Newer versions
of mysql will also choke on such queries I believe as well. Didn't
have time to go through your whole query but that's what to look for.