Re: upgrading postgresql broke some queries

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Aug 4, 2009 at 1:02 PM, Aras Angelo<araskoktas@xxxxxxxxx> 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.

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


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux