Thread: Trying to create implicit casts to text in PG 8.3
Hi, everyone. I'm trying to upgrade a database which is used by several hundred installations of an app; with each installation possibly running some custom code and 3rd party extensions. I was hoping that it would be possible to re-add implicit casts to text for bacwards compatibility with 8.2, since that would be much easier and smoother than testing all those installations and/or waiting for bug reports to start pouring in... I tried adding casts found here: http://code.open-bio.org/svnweb/index.cgi/biosql/revision?rev=284 Function calls work fine now, but a new problem appeared with operators: SELECT EXTRACT(DAY FROM NOW()) || 'abc'; ERROR: operator is not unique: double precision || unknown Is there a way to get around this? Regards, Aleksander
2009/1/13 Aleksander Kmetec <aleksander.kmetec@intera.si>: > Hi, everyone. > > I'm trying to upgrade a database which is used by several hundred > installations of an app; with each installation possibly running some custom > code and 3rd party extensions. I was hoping that it would be possible to > re-add implicit casts to text for bacwards compatibility with 8.2, since > that would be much easier and smoother than testing all those installations > and/or waiting for bug reports to start pouring in... > > I tried adding casts found here: > http://code.open-bio.org/svnweb/index.cgi/biosql/revision?rev=284 > > Function calls work fine now, but a new problem appeared with operators: > SELECT EXTRACT(DAY FROM NOW()) || 'abc'; > ERROR: operator is not unique: double precision || unknown > > Is there a way to get around this? > You add the cast's but not the operator. The cast is in other way (try to compare with a text '13') for example. > > Regards, > Aleksander > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Emanuel Calvo Franco ArPUG / AOSUG Member Postgresql Support & Admin
Emanuel Calvo Franco wrote: > You add the cast's but not the operator. > The cast is in other way (try to compare with a text '13') > for example. Thanks. I added both "||(double precision, text)" and "||(text, double precision)" operators and it works now. But I'm wondering: do I need to do anything else besides creating implicit casts and adding missing operators? And will this introduce any side effects which are not compatible with 8.2 and might break my queries in unpredictable ways? Regards, Aleksander
On Tue, Jan 13, 2009 at 05:43:52PM +0100, Aleksander Kmetec wrote: > I added both "||(double precision, text)" and "||(text, double > precision)" operators and it works now. > > But I'm wondering: do I need to do anything else besides creating implicit > casts and adding missing operators? And will this introduce any side > effects which are not compatible with 8.2 and might break my queries in > unpredictable ways? You'll get less errors from PG because you've told it to convert things automatically for you. I'd be tempted to not do this and change the code to make these conversion explicit as there will be cases (maybe when you're writing code in the future) when you want to get an error and not have PG do something unexpected. In the end, any type system is just a tool. It's main job is to find bugs in code by spotting a common class of error (it also allows other optimizations, but that's normally less important). I tend to find it serves a useful purpose and the errors it gives are indications for me to be more explicit with my code. If you find it's too strict you're free to disable it by adding in extra casts/functions/operators. Sam
On Tue, 2009-01-13 at 16:58 +0000, Sam Mason wrote: > In the end, any type system is just a tool. It's main job is to find > bugs in code by spotting a common class of error The purpose of the database as a whole is to preserve the integrity of your data. The type system is a key component of that. The main job of the type system is to assist in insuring that your data is correct. Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
On Tue, Jan 13, 2009 at 09:14:15AM -0800, Joshua D. Drake wrote: > On Tue, 2009-01-13 at 16:58 +0000, Sam Mason wrote: > > In the end, any type system is just a tool. It's main job is to find > > bugs in code by spotting a common class of error > > The purpose of the database as a whole is to preserve the integrity of > your data. The type system is a key component of that. The main job of > the type system is to assist in insuring that your data is correct. I think we're saying the same thing, but just to make sure: it's technically possible (but practically *very* difficult) to "preserve the integrity of your data" without having any type system. Knowing this, languages have some type system (either statically enforced at compile time, or dynamically checked during interpretation, or some mixture of both) to ask us for clarification when we've written some code that looks "a bit dubious". As a side note, modern languages have extended types a long way. Some to the extent that you can program at the type level, giving the user many more tools to constrain the dynamic aspects of their code. One fun experiment I've never really managed to get my head around is Chameleon[1]. Sam [1] http://taichi.ddns.comp.nus.edu.sg/taichiwiki/ChameleonHomePage