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

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
Message-ID: 201010291502.15489.scott.newton@vadacom.co.nz (view raw or flat)
Thread:
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 cdr.card_id=cc.id 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 
tf.dnid::text=substr(cdr.dnid,1,length(tf.dnid::text))

still gives

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

Thanks

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

In response to

Responses

pgsql-general by date

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

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