pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedType Conversion Error after migrating from 8.3 to 9.2
2+ messages / 2 participants
[nested] [flat]
* Type Conversion Error after migrating from 8.3 to 9.2
@ 2013-05-13 11:04 Hariraman Jayaraj <ocpdreams@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Hariraman Jayaraj @ 2013-05-13 11:04 UTC (permalink / raw)
To: pgsql-admin
Dear Team,
We are using Postgres 9.2 in suse linux 11 Enterprise server.
Recently we have migrated from 8.3 to 9.2 directly using pg_dump and
pg_restore as we are not using much postgres extensions.
Restore was successful and our application running fine.
But in some inserts where we use like
*to_timestamp('8-5-2013 22:00:02','dd-mm-yyyy hh:mi:ss')::TIMESTAMP*
it throws error as
hour "22" is invalid for the 12-hour clock.
But the same insert is working with version 8.3
Only after modifying the to_timestamp with 24hour clock i.e.,
*to_timestamp('8-5-2013 22:00:02','dd-mm-yyyy hh24:mi:ss')::TIMESTAMP*, its
working fine in version 9.2
Is there any other workaround available instead of modifying multiple
queries as lot of our application coding has this conversion.
Thanks in advance.
Thanks,
Franc.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Type Conversion Error after migrating from 8.3 to 9.2
@ 2013-05-13 13:10 Richard Poole <rp@guests.deus.net>
parent: Hariraman Jayaraj <ocpdreams@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Poole @ 2013-05-13 13:10 UTC (permalink / raw)
To: pgsql-admin
On Mon, May 13, 2013 at 04:34:14PM +0530, Hariraman Jayaraj wrote:
> But in some inserts where we use like
> *to_timestamp('8-5-2013 22:00:02','dd-mm-yyyy hh:mi:ss')::TIMESTAMP*
> it throws error as
> hour "22" is invalid for the 12-hour clock.
The 'hh' pattern has always been documented as meaning an hour no more
than 12 (equivalent to 'hh12'); it was actually a side-effect of the fix
to accept 'am' and 'pm' in either upper or lower case that made the code
start throwing errors rather than silently doing what you expected.
> But the same insert is working with version 8.3
> Only after modifying the to_timestamp with 24hour clock i.e.,
> *to_timestamp('8-5-2013 22:00:02','dd-mm-yyyy hh24:mi:ss')::TIMESTAMP*, its
> working fine in version 9.2
> Is there any other workaround available instead of modifying multiple
> queries as lot of our application coding has this conversion.
One possible workaround would be to define your own to_timestamp function
implementing the behaviour you want:
create or replace function public.to_timestamp(text, text)
returns timestamp with time zone as
$$select pg_catalog.to_timestamp($1, replace($2, 'hh', 'hh24'))$$
language 'sql';
then fix your schema search path so that your new function is the
one that gets hit by an unqualified call to to_timestamp:
set search_path to "$user",public,pg_catalog;
(that is how you would do it in psql; for real live use you would
presumably use ALTER ROLE so each connection has the correct path
by default)
and now your insert will do as you want. This is fraught with danger,
though, because now objects in your public schema will all be seen before
objects in pg_catalog so any name clashes you may have been unaware of
will suddenly reverse their behaviour. So you should definitely start
fixing your code. But this workaround might help you in the short term.
Richard
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2013-05-13 13:10 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-05-13 11:04 Type Conversion Error after migrating from 8.3 to 9.2 Hariraman Jayaraj <ocpdreams@gmail.com>
2013-05-13 13:10 ` Richard Poole <rp@guests.deus.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox