Scott Newton <scott.newton@xxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
