Re: MySQL -> PostgreSQL conversion issue

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Scott Newton <scott(dot)newton(at)vadacom(dot)co(dot)nz>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: MySQL -> PostgreSQL conversion issue
Date: 2010-10-29 01:40:51
Message-ID: 12919.1288316451@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Scott Newton <scott(dot)newton(at)vadacom(dot)co(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Newton 2010-10-29 02:02:14 Re: MySQL -> PostgreSQL conversion issue
Previous Message Dann Corbit 2010-10-29 01:39:33 Re: MySQL -> PostgreSQL conversion issue