Skip site navigation (1) Skip section navigation (2)

Re: MySQL -> PostgreSQL conversion issue

From: Dann Corbit <DCorbit(at)connx(dot)com>
To: 'Scott Marlowe' <scott(dot)marlowe(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org"<pgsql-general(at)postgresql(dot)org>
Subject: Re: MySQL -> PostgreSQL conversion issue
Date: 2010-10-29 01:39:33
Message-ID: 87F42982BF2B434F831FCEF4C45FC33E4206C2BA@EXCHANGE.corporate.connx.com (view raw or flat)
Thread:
Lists: pgsql-general
> -----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.

In response to

pgsql-general by date

Next:From: Tom LaneDate: 2010-10-29 01:40:51
Subject: Re: MySQL -> PostgreSQL conversion issue
Previous:From: Scott MarloweDate: 2010-10-29 01:31:26
Subject: Re: MySQL -> PostgreSQL conversion issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group