Re: MySQL -> PostgreSQL conversion issue

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

On Fri, 29 Oct 2010 14:40:51 Tom Lane wrote:
> [ 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.

Agreed - it's not very pretty.

> 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))

Unfortunately not:
from cc_call cdr left join cc_card cc on left join
(select 'dnid' as dnid, 0.1 as sell_cost,0.1 as cost,0
as dnid_type union select 8887798764,0.02,0.06 ,1
) as tf on

still gives

2010-10-29 14:58:09 NZDT ERROR: invalid input syntax for integer: "dnid"


Scott Newton
Software Engineer @ Vadacom Limited
Ph: (09) 9690600 x 280

In response to


Browse pgsql-general by date

  From Date Subject
Next Message Karl Pickett 2010-10-29 02:04:48 Can Postgres Not Do This Safely ?!?
Previous Message Tom Lane 2010-10-29 01:40:51 Re: MySQL -> PostgreSQL conversion issue