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

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 (view raw or flat)
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

pgsql-general by date

Next:From: Scott NewtonDate: 2010-10-29 02:02:14
Subject: Re: MySQL -> PostgreSQL conversion issue
Previous:From: Dann CorbitDate: 2010-10-29 01:39:33
Subject: Re: MySQL -> PostgreSQL conversion issue

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