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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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
> > ( 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

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2010-10-29 01:40:51 Re: MySQL -> PostgreSQL conversion issue
Previous Message Scott Marlowe 2010-10-29 01:31:26 Re: MySQL -> PostgreSQL conversion issue