Обсуждение: Legacy app not working with newer PG, is there a quick fix?
I have a legacy client java application that I try to run against a new Postgres server. All data migrates as expected. Everything seems to work ok, except for one thing. ERROR: function to_date(timestamp without time zone, unknown) does not exist at character 9 HINT: No function matches the given name and argument types. You might need to add explicit type casts. The application was written with and works with PG 8.2.3 I understand that from PG 8.3 explicit typecasting is necessary, like: to_date(cast(createddate as TEXT),'YYYY-MM-DD’) But I do not have access to the source code. Is there a way to go around this, by creating a macro & function to providethe type cast on the server side. Without having to change anything in the client application? /Lars
On Fri, Feb 8, 2019, 11:40 PM Lars Gustafsson <gumse@me.com> wrote:
I have a legacy client java application that I try to run against a new Postgres server.
All data migrates as expected. Everything seems to work ok, except for one thing.
ERROR: function to_date(timestamp without time zone, unknown) does not exist at character 9
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
The application was written with and works with PG 8.2.3
I understand that from PG 8.3 explicit typecasting is necessary, like:
to_date(cast(createddate as TEXT),'YYYY-MM-DD’)
You could write a function say my_schema.to_date which essentially calls and returns output of pg_catalog.to_date(cast($1 as TEXT),'YYYY-MM-DD’)
In your config change the search path to give priority to the overridding function :
search_path=my_schema,pg_catalog
But remember that it is a bad hack and any expression index you have might not give you a benefit or might need to be rebuilt after these changes.
But I do not have access to the source code. Is there a way to go around this, by creating a macro & function to provide the type cast on the server side.
Without having to change anything in the client application?
Yes, as said above. But there would be a cost for it on performance side.
/Lars
Sameer Kumar | Senior Solution Architect
ASHNIK PTE. LTD.
36 Robinson Road, #14-04 City House, Singapore 068877
T: +65 6438 3504 | www.ashnik.com
Skype: sameer.ashnik | M: +65 8110 0350
Am 08.02.19 um 16:27 schrieb Lars Gustafsson: > I have a legacy client java application that I try to run against a new Postgres server. > All data migrates as expected. Everything seems to work ok, except for one thing. > > ERROR: function to_date(timestamp without time zone, unknown) does not exist at character 9 > HINT: No function matches the given name and argument types. You might need to add explicit type casts. > > The application was written with and works with PG 8.2.3 > > I understand that from PG 8.3 explicit typecasting is necessary, like: > to_date(cast(createddate as TEXT),'YYYY-MM-DD’) > http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com