Re: MySQL -> PostgreSQL conversion issue

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Scott Marlowe
> Sent: Thursday, October 28, 2010 6:31 PM
> To: Scott Newton
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] 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.

If the operation is a substring, and if tf.dnid is a bigint, then the query is absurd.
The data length of a bigint will be 8 bytes. Length has no meaning since tf.dnid is not a string. Also, assignment of a substring to a bigint is a rather silly operation.

Probably what the o.p. wants is just:
tf.dnid = cdr.dnid::bigint
But that assumes that the types bigint and varchar and the o.p. wants to store the number contained in the string into the big integer.

I would hate to see the rest of the design. I am ill from this tiny fragment.