Re: MySQL -> PostgreSQL conversion issue - Mailing list pgsql-general

From Tom Lane
Subject Re: MySQL -> PostgreSQL conversion issue
Date
Msg-id 12919.1288316451@sss.pgh.pa.us
Whole thread Raw
In response to MySQL -> PostgreSQL conversion issue  (Scott Newton <scott.newton@vadacom.co.nz>)
Responses Re: MySQL -> PostgreSQL conversion issue
List pgsql-general
Scott Newton <scott.newton@vadacom.co.nz> writes:
> 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).

[ blanch... ]  That's not the worst SQL code I've ever seen, but it
might be in the top ten.  They're apparently trying to see whether
tf.dnid, taken as a string, matches a prefix of cdr.dnid --- but what if
you have say 123 versus "1234foo"?  This will match, but most likely
it shouldn't.  They need to acquire a clue, and a better data
representation.

Anyway, you've got two different typing violations there, so you need
two casts to fix it:

as tf on tf.dnid::text = substr(cdr.dnid,1,length(tf.dnid::text))

(salt to taste with CAST and/or varchar if you prefer, but it's
all the same)

            regards, tom lane

pgsql-general by date:

Previous
From: Dann Corbit
Date:
Subject: Re: MySQL -> PostgreSQL conversion issue
Next
From: Scott Newton
Date:
Subject: Re: MySQL -> PostgreSQL conversion issue