Обсуждение: upgrading postgresql broke some queries
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
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
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.html#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.sql Regards, -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org
Вложения
On Tue, Aug 4, 2009 at 1:02 PM, Aras Angelo<araskoktas@gmail.com> wrote: > 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. 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.