> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Scott Marlowe > Sent: Thursday, October 28, 2010 6:31 PM > To: Scott Newton > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: MySQL -> PostgreSQL conversion issue > > On Thu, Oct 28, 2010 at 6:44 PM, Scott Newton > <scott.newton@xxxxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
