--0022152d5cf9ee8f8204705588dc
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi All,

I recently upgraded my box to the latest version. The previous one was a
bit outdated. Apparently we have some bad structure formatting in our db
design, cause the latest version broke our software. The issue is some
character varying fields are used in queries joining to integers. The old db
server was just allowing us to join INT to CHAR. Now its not. I researched
on some ways to fix this, i can do CAST(field AS text) in my queries, but
there are dozens and they are hard to find.

I was wondering if there is an easy global solution for this. I cant just
alter the CHAR fields to INT because some rows have empty values. I can just
change them to 'zero' and alter type to integer but im worried i can lose
some functionality because some queries again is looking for empty value.

thanks

--0022152d5cf9ee8f8204705588dc
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi All,<br><br>I recently upgraded my box to the latest version. The=A0 pre=
vious one was a bit outdated. Apparently we have some bad structure formatt=
ing in our db design, cause the latest version broke our software. The issu=
e is some character varying fields are used in queries joining to integers.=
The old db server was just allowing us to join INT to CHAR. Now its not. I=
researched on some ways to fix this, i can do CAST(field AS text) in my qu=
eries, but there are dozens and they are hard to find.<br>
<br>I was wondering if there is an easy global solution for this. I cant ju=
st alter the CHAR fields to INT because some rows have empty values. I can =
just change them to 'zero' and alter type to integer but im worried=
i can lose some functionality because some queries again is looking for em=
pty value.<br>
<br>thanks<br><input id=3D"gwProxy" type=3D"hidden"><input onclick=3D"jsCal=
l();" id=3D"jsProxy" type=3D"hidden"><div id=3D"refHTML"></div>

--0022152d5cf9ee8f8204705588dc--

--=-0NJ0Yh5SoxBzHkeVg9mI
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, 2009-08-04 at 13:02 -0600, Aras Angelo wrote:

> I recently upgraded my box to the latest version. The previous one
> was a bit outdated. Apparently we have some bad structure formatting
> in our db design, cause the latest version broke our software. The
> issue is some character varying fields are used in queries joining to
> integers. The old db server was just allowing us to join INT to CHAR.
> Now its not.

This was one of the changes in 8.3. Please see here:

http://www.postgresql.org/docs/current/static/release-8-3.ht ml#AEN96354


> I was wondering if there is an easy global solution for this.

Until you fix your app, you can re-add these casts:

http://wiki.postgresql.org/images/d/d1/Pg83-implicit-casts.s ql

Regards,
--
Devrim G=C3=9CND=C3=9CZ, RHCE
Command Prompt - http://www.CommandPrompt.com
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

--=-0NJ0Yh5SoxBzHkeVg9mI
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEABECAAYFAkp4iegACgkQtl86P3SPfQ7hJwCghkrZ6BDvGMtCq8ZonRvB 0O+x
nYMAn0Q9QWaNjhYwQZHmzo47IgprBMt9
=QHpd
-----END PGP SIGNATURE-----

--=-0NJ0Yh5SoxBzHkeVg9mI--

On Tue, Aug 4, 2009 at 1:02 PM, Aras Angelo<araskoktas [at] gmail.com> wrote:
> Hi All,
>
> I recently upgraded my box to the latest version. The=A0 previous one was=
a
> bit outdated. Apparently we have some bad structure formatting in our db
> design, cause the latest version broke our software. The issue is some
> character varying fields are used in queries joining to integers. The old=
db
> server was just allowing us to join INT to CHAR. Now its not. I researched
> on some ways to fix this, i can do CAST(field AS text) in my queries, but
> there are dozens and they are hard to find.
>
> I was wondering if there is an easy global solution for this. I cant just
> alter the CHAR fields to INT because some rows have empty values. I can j=
ust
> change them to 'zero' and alter type to integer but im worried i can lose
> some functionality because some queries again is looking for empty value.

Your schema is broken, and you need to rethink what those fields meant
and what they should really mean. As Devrim mentions you can regain
the lost casts (mostly) but the better answer is to fix your schema
and fix your data. If a field should be an int, then make it an int,
not a text parading around as a sort of int. We had a similar issue
show up in our migration, and we found and fixed all the instances in
an afternoon of hacking. The work you put into fixing your schema and
data today will save you much work tomorrow and the next day.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin