pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCreating redwood datestyle in Postgres 12
17+ messages / 9 participants
[nested] [flat]
* Creating redwood datestyle in Postgres 12
@ 2021-03-24 23:18 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 17+ messages in thread
From: Tim @ 2021-03-24 23:18 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello everyone,
Has anyone ever had to migrate Oracle redwood style dates functionality
into Postgres? Is it possible to set a global custom DateStyle setting?
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Creating redwood datestyle in Postgres 12
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
@ 2021-03-25 04:06 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-25 12:38 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Laurenz Albe @ 2021-03-25 04:06 UTC (permalink / raw)
To: Tim <timfosho@gmail.com>; pgsql-admin@lists.postgresql.org
On Wed, 2021-03-24 at 19:18 -0400, Tim wrote:
> Has anyone ever had to migrate Oracle redwood style dates functionality into Postgres?
> Is it possible to set a global custom DateStyle setting?
What would that be?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Creating redwood datestyle in Postgres 12
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
@ 2021-03-25 12:38 ` Tim <timfosho@gmail.com>
2021-03-25 17:02 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-26 09:16 ` Re: Creating redwood datestyle in Postgres 12 Paul Smith <paul@pscs.co.uk>
0 siblings, 2 replies; 17+ messages in thread
From: Tim @ 2021-03-25 12:38 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
Its a native ORACLE date formatting which shows like this: 08-JAN-99
04:05:06 set in the datestyle config param.
Currently running on EDB Advanced Server 12 which allows it as a datestyle
configuration setting, but looking to migrate off of there to native
postgres pretty soon and was hoping there was some way to have the same
setting globally in native postgres. Which is starting to look like is not
possible.
Thank you!
Tim A
On Thu, Mar 25, 2021 at 12:06 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Wed, 2021-03-24 at 19:18 -0400, Tim wrote:
> > Has anyone ever had to migrate Oracle redwood style dates functionality
> into Postgres?
> > Is it possible to set a global custom DateStyle setting?
>
> What would that be?
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Creating redwood datestyle in Postgres 12
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-25 12:38 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
@ 2021-03-25 17:02 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 17+ messages in thread
From: Laurenz Albe @ 2021-03-25 17:02 UTC (permalink / raw)
To: Tim <timfosho@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Thu, 2021-03-25 at 08:38 -0400, Tim wrote:
> Its a native ORACLE date formatting which shows like this: 08-JAN-99 04:05:06 set in the datestyle config param.
>
> Currently running on EDB Advanced Server 12 which allows it as a datestyle configuration setting,
> but looking to migrate off of there to native postgres pretty soon and was hoping there was some
> way to have the same setting globally in native postgres. Which is starting to look like is not possible.
I concur.
You should use "to_char" in your queries or format timestamps in the client application.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Creating redwood datestyle in Postgres 12
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-25 12:38 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
@ 2021-03-26 09:16 ` Paul Smith <paul@pscs.co.uk>
2021-03-26 12:24 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Paul Smith @ 2021-03-26 09:16 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 25/03/2021 12:38, Tim wrote:
> Its a native ORACLE date formatting which shows like this: 08-JAN-99
> 04:05:06 set in the datestyle config param.
>
> Currently running on EDB Advanced Server 12 which allows it as a
> datestyle configuration setting, but looking to migrate off of there
> to native postgres pretty soon and was hoping there was some way to
> have the same setting globally in native postgres. Which is starting
> to look like is not possible.
Could you create views/rules/triggers to do the conversions for you?
Eg, if you have a table 'invoices', then create a view 'invoices_' and
set it to set your formatting, and use rules to allow updates/inserts
via that view. That might need less changes to the code.
--
Paul
Paul Smith Computer Services
support@pscs.co.uk - 01484 855800
--
Paul Smith Computer Services
Tel: 01484 855800
Vat No: GB 685 6987 53
Sign up for news & updates at http://www.pscs.co.uk/go/subscribe
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Creating redwood datestyle in Postgres 12
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-25 12:38 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 09:16 ` Re: Creating redwood datestyle in Postgres 12 Paul Smith <paul@pscs.co.uk>
@ 2021-03-26 12:24 ` Tim <timfosho@gmail.com>
2021-03-26 12:35 ` Re: Creating redwood datestyle in Postgres 12 Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 17+ messages in thread
From: Tim @ 2021-03-26 12:24 UTC (permalink / raw)
To: Paul Smith <paul@pscs.co.uk>; +Cc: pgsql-admin@lists.postgresql.org
I suppose that would be possible, but maintaining date triggers + views
across a large database would be a lot of work. I'm just one humble DBA and
there is 15-20 devs
On Fri, Mar 26, 2021 at 5:21 AM Paul Smith <paul@pscs.co.uk> wrote:
> On 25/03/2021 12:38, Tim wrote:
> > Its a native ORACLE date formatting which shows like this: 08-JAN-99
> > 04:05:06 set in the datestyle config param.
> >
> > Currently running on EDB Advanced Server 12 which allows it as a
> > datestyle configuration setting, but looking to migrate off of there
> > to native postgres pretty soon and was hoping there was some way to
> > have the same setting globally in native postgres. Which is starting
> > to look like is not possible.
> Could you create views/rules/triggers to do the conversions for you?
>
> Eg, if you have a table 'invoices', then create a view 'invoices_' and
> set it to set your formatting, and use rules to allow updates/inserts
> via that view. That might need less changes to the code.
>
>
> --
> Paul
> Paul Smith Computer Services
> support@pscs.co.uk - 01484 855800
>
>
> --
>
>
> Paul Smith Computer Services
> Tel: 01484 855800
> Vat No: GB 685 6987 53
>
> Sign up for news & updates at http://www.pscs.co.uk/go/subscribe
>
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Creating redwood datestyle in Postgres 12
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-25 12:38 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 09:16 ` Re: Creating redwood datestyle in Postgres 12 Paul Smith <paul@pscs.co.uk>
2021-03-26 12:24 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
@ 2021-03-26 12:35 ` Bruce Momjian <bruce@momjian.us>
2021-03-26 13:42 ` Re: Creating redwood datestyle in Postgres 12 Tom Lane <tgl@sss.pgh.pa.us>
2021-03-26 16:55 ` Re: Creating redwood datestyle in Postgres 12 legrand legrand <legrand_legrand@hotmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Bruce Momjian @ 2021-03-26 12:35 UTC (permalink / raw)
To: Tim <timfosho@gmail.com>; +Cc: Paul Smith <paul@pscs.co.uk>; pgsql-admin@lists.postgresql.org
On Fri, Mar 26, 2021 at 08:24:37AM -0400, Tim wrote:
> I suppose that would be possible, but maintaining date triggers + views across
> a large database would be a lot of work. I'm just one humble DBA and there is
> 15-20 devs
You probably need a custom data type with a custom C output function do
to this efficiently.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Creating redwood datestyle in Postgres 12
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-25 12:38 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 09:16 ` Re: Creating redwood datestyle in Postgres 12 Paul Smith <paul@pscs.co.uk>
2021-03-26 12:24 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 12:35 ` Re: Creating redwood datestyle in Postgres 12 Bruce Momjian <bruce@momjian.us>
@ 2021-03-26 13:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-03-26 15:27 ` Re: Creating redwood datestyle in Postgres 12 Jonah H. Harris <jonah.harris@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Tom Lane @ 2021-03-26 13:42 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Tim <timfosho@gmail.com>; Paul Smith <paul@pscs.co.uk>; pgsql-admin@lists.postgresql.org
Bruce Momjian <bruce@momjian.us> writes:
> On Fri, Mar 26, 2021 at 08:24:37AM -0400, Tim wrote:
>> I suppose that would be possible, but maintaining date triggers + views across
>> a large database would be a lot of work. I'm just one humble DBA and there is
>> 15-20 devs
> You probably need a custom data type with a custom C output function do
> to this efficiently.
If you're willing to write C code it likely wouldn't be that hard
to modify datetime output to honor a new datestyle value. (Input
is messier, but since we already read this format okay, I don't
think anything is needed on that end.)
It would seem reasonable to me to have a datestyle that emits
the month as a 3-letter abbreviation, so that you could get
'2021-Mar-26' or '26-Mar-2021' etc depending on the YMD/DMY/MDY
setting. I'd draw the line at emitting 2-digit years though.
(If you need to be that bug-compatible with Oracle, you should
stick with EDB --- that's what their AS product is for.)
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Creating redwood datestyle in Postgres 12
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-25 12:38 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 09:16 ` Re: Creating redwood datestyle in Postgres 12 Paul Smith <paul@pscs.co.uk>
2021-03-26 12:24 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 12:35 ` Re: Creating redwood datestyle in Postgres 12 Bruce Momjian <bruce@momjian.us>
2021-03-26 13:42 ` Re: Creating redwood datestyle in Postgres 12 Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-03-26 15:27 ` Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Jonah H. Harris @ 2021-03-26 15:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <bruce@momjian.us>; Tim <timfosho@gmail.com>; Paul Smith <paul@pscs.co.uk>; pgsql-admin@lists.postgresql.org
On Fri, Mar 26, 2021 at 9:42 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > On Fri, Mar 26, 2021 at 08:24:37AM -0400, Tim wrote:
> >> I suppose that would be possible, but maintaining date triggers + views
> across
> >> a large database would be a lot of work. I'm just one humble DBA and
> there is
> >> 15-20 devs
>
> > You probably need a custom data type with a custom C output function do
> > to this efficiently.
>
> If you're willing to write C code it likely wouldn't be that hard
> to modify datetime output to honor a new datestyle value. (Input
> is messier, but since we already read this format okay, I don't
> think anything is needed on that end.)
>
> It would seem reasonable to me to have a datestyle that emits
> the month as a 3-letter abbreviation, so that you could get
> '2021-Mar-26' or '26-Mar-2021' etc depending on the YMD/DMY/MDY
> setting. I'd draw the line at emitting 2-digit years though.
> (If you need to be that bug-compatible with Oracle, you should
> stick with EDB --- that's what their AS product is for.)
>
IIRC, in 2005-2008, the Oracle-style date/time format code was < 50 LoC
total as PG already supports almost everything needed. After a cursory look
through those parts of the PG code, I'd guess this is still < 100 LoC
fully-functional. But, for the reasons Tom said, this is something that
wouldn't make it into community PG. Of all the EDB features, this is the
easiest to implement if you were OK running a patched version. I'm sure
several consultants on here would write you a patch fairly quickly for a
fee.
--
Jonah H. Harris
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Creating redwood datestyle in Postgres 12
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-25 12:38 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 09:16 ` Re: Creating redwood datestyle in Postgres 12 Paul Smith <paul@pscs.co.uk>
2021-03-26 12:24 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 12:35 ` Re: Creating redwood datestyle in Postgres 12 Bruce Momjian <bruce@momjian.us>
@ 2021-03-26 16:55 ` legrand legrand <legrand_legrand@hotmail.com>
2021-03-26 17:27 ` Re: Creating redwood datestyle in Postgres 12 Jonah H. Harris <jonah.harris@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: legrand legrand @ 2021-03-26 16:55 UTC (permalink / raw)
To: pgsql-admin
Hi,
Isn’t this alteady proposed in extension oracfe ?
https://github.com/orafce/orafce
Regards
PAscal
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Creating redwood datestyle in Postgres 12
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-25 12:38 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 09:16 ` Re: Creating redwood datestyle in Postgres 12 Paul Smith <paul@pscs.co.uk>
2021-03-26 12:24 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 12:35 ` Re: Creating redwood datestyle in Postgres 12 Bruce Momjian <bruce@momjian.us>
2021-03-26 16:55 ` Re: Creating redwood datestyle in Postgres 12 legrand legrand <legrand_legrand@hotmail.com>
@ 2021-03-26 17:27 ` Jonah H. Harris <jonah.harris@gmail.com>
2021-03-26 20:21 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Jonah H. Harris @ 2021-03-26 17:27 UTC (permalink / raw)
To: legrand legrand <legrand_legrand@hotmail.com>; +Cc: pgsql-admin
On Fri, Mar 26, 2021 at 12:55 PM legrand legrand <
legrand_legrand@hotmail.com> wrote:
> Isn’t this alteady proposed in extension oracfe ?
> https://github.com/orafce/orafce
I don't believe this can be done implicitly, across the entire server, by
default via an extension. It could be done on a table-by-table or
view-level basis using custom formatting (via a function), which it seems
Tim would like to avoid.
--
Jonah H. Harris
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Creating redwood datestyle in Postgres 12
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-25 12:38 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 09:16 ` Re: Creating redwood datestyle in Postgres 12 Paul Smith <paul@pscs.co.uk>
2021-03-26 12:24 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 12:35 ` Re: Creating redwood datestyle in Postgres 12 Bruce Momjian <bruce@momjian.us>
2021-03-26 16:55 ` Re: Creating redwood datestyle in Postgres 12 legrand legrand <legrand_legrand@hotmail.com>
2021-03-26 17:27 ` Re: Creating redwood datestyle in Postgres 12 Jonah H. Harris <jonah.harris@gmail.com>
@ 2021-03-26 20:21 ` Tim <timfosho@gmail.com>
2021-03-26 20:23 ` Re: Creating redwood datestyle in Postgres 12 Bruce Momjian <bruce@momjian.us>
2021-03-27 00:19 ` Re: Creating redwood datestyle in Postgres 12 jim schmidt <txherper@gmail.com>
0 siblings, 2 replies; 17+ messages in thread
From: Tim @ 2021-03-26 20:21 UTC (permalink / raw)
To: Jonah H. Harris <jonah.harris@gmail.com>; +Cc: legrand legrand <legrand_legrand@hotmail.com>; pgsql-admin
Yes anything that involves management at a table level is not practical due
to the size of our database and limited amount of resources (me). I was not
aware of the orafce extension and will keep it in mind.
Tom, we currently are on EDB AS 12 but its becoming cost inefficient to
stay with EDB due to our scale, since their pricing reflects the size of
the production VMs that are used. So while this migration might be a pain
from the application side.. long run it would be cheaper.
Jonah, I'm assuming a patch would only apply per Postgres version... so any
future PG version upgrade would require a new patch? While I'm sure
technically its doable.. long run it would be more practical to just move
away from these cursed Oracle dates.
Thanks for your help everyone!
On Fri, Mar 26, 2021 at 1:27 PM Jonah H. Harris <jonah.harris@gmail.com>
wrote:
> On Fri, Mar 26, 2021 at 12:55 PM legrand legrand <
> legrand_legrand@hotmail.com> wrote:
>
>> Isn’t this alteady proposed in extension oracfe ?
>> https://github.com/orafce/orafce
>
>
> I don't believe this can be done implicitly, across the entire server, by
> default via an extension. It could be done on a table-by-table or
> view-level basis using custom formatting (via a function), which it seems
> Tim would like to avoid.
>
> --
> Jonah H. Harris
>
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Creating redwood datestyle in Postgres 12
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-25 12:38 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 09:16 ` Re: Creating redwood datestyle in Postgres 12 Paul Smith <paul@pscs.co.uk>
2021-03-26 12:24 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 12:35 ` Re: Creating redwood datestyle in Postgres 12 Bruce Momjian <bruce@momjian.us>
2021-03-26 16:55 ` Re: Creating redwood datestyle in Postgres 12 legrand legrand <legrand_legrand@hotmail.com>
2021-03-26 17:27 ` Re: Creating redwood datestyle in Postgres 12 Jonah H. Harris <jonah.harris@gmail.com>
2021-03-26 20:21 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
@ 2021-03-26 20:23 ` Bruce Momjian <bruce@momjian.us>
2021-03-26 22:34 ` Re: Creating redwood datestyle in Postgres 12 Jonah H. Harris <jonah.harris@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Bruce Momjian @ 2021-03-26 20:23 UTC (permalink / raw)
To: Tim <timfosho@gmail.com>; +Cc: Jonah H. Harris <jonah.harris@gmail.com>; legrand legrand <legrand_legrand@hotmail.com>; pgsql-admin
On Fri, Mar 26, 2021 at 04:21:13PM -0400, Tim wrote:
> Yes anything that involves management at a table level is not practical due to
> the size of our database and limited amount of resources (me). I was not aware
> of the orafce extension and will keep it in mind.
>
> Tom, we currently are on EDB AS 12 but its becoming cost inefficient to stay
> with EDB due to our scale, since their pricing reflects the size of the
> production VMs that are used. So while this migration might be a pain from the
> application side.. long run it would be cheaper.
>
> Jonah, I'm assuming a patch would only apply per Postgres version... so any
> future PG version upgrade would require a new patch? While I'm sure technically
> its doable.. long run it would be more practical to just move away from these
> cursed Oracle dates.
Yes, you might be able to compile it yourself for each version, but if
it doesn't compile, someone will have to fix it.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Creating redwood datestyle in Postgres 12
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-25 12:38 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 09:16 ` Re: Creating redwood datestyle in Postgres 12 Paul Smith <paul@pscs.co.uk>
2021-03-26 12:24 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 12:35 ` Re: Creating redwood datestyle in Postgres 12 Bruce Momjian <bruce@momjian.us>
2021-03-26 16:55 ` Re: Creating redwood datestyle in Postgres 12 legrand legrand <legrand_legrand@hotmail.com>
2021-03-26 17:27 ` Re: Creating redwood datestyle in Postgres 12 Jonah H. Harris <jonah.harris@gmail.com>
2021-03-26 20:21 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 20:23 ` Re: Creating redwood datestyle in Postgres 12 Bruce Momjian <bruce@momjian.us>
@ 2021-03-26 22:34 ` Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Jonah H. Harris @ 2021-03-26 22:34 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Tim <timfosho@gmail.com>; legrand legrand <legrand_legrand@hotmail.com>; pgsql-admin
On Fri, Mar 26, 2021 at 4:23 PM Bruce Momjian <bruce@momjian.us> wrote:
> On Fri, Mar 26, 2021 at 04:21:13PM -0400, Tim wrote:
> > Jonah, I'm assuming a patch would only apply per Postgres version... so
> any
> > future PG version upgrade would require a new patch? While I'm sure
> technically
> > its doable.. long run it would be more practical to just move away from
> these
> > cursed Oracle dates.
>
> Yes, you might be able to compile it yourself for each version, but if
> it doesn't compile, someone will have to fix it.
This.
--
Jonah H. Harris
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Creating redwood datestyle in Postgres 12
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-25 12:38 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 09:16 ` Re: Creating redwood datestyle in Postgres 12 Paul Smith <paul@pscs.co.uk>
2021-03-26 12:24 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 12:35 ` Re: Creating redwood datestyle in Postgres 12 Bruce Momjian <bruce@momjian.us>
2021-03-26 16:55 ` Re: Creating redwood datestyle in Postgres 12 legrand legrand <legrand_legrand@hotmail.com>
2021-03-26 17:27 ` Re: Creating redwood datestyle in Postgres 12 Jonah H. Harris <jonah.harris@gmail.com>
2021-03-26 20:21 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
@ 2021-03-27 00:19 ` jim schmidt <txherper@gmail.com>
2021-03-27 02:15 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: jim schmidt @ 2021-03-27 00:19 UTC (permalink / raw)
To: Tim <timfosho@gmail.com>; +Cc: Jonah H. Harris <jonah.harris@gmail.com>; legrand legrand <legrand_legrand@hotmail.com>; pgsql-admin
I am confused, don't dates that traverse the network get transported in
canonical format and the client format the date?
This obviously is not the case when one applies a to_char function but in
jdbc the canonical format is translated to a java.sql.Date and formatting
is a client responsibility. Same applies with pro*c.
I would think if the formatting is done by the client that a server patch
would do nothing.
Also creating to_char views and programmatically is a trivial operation.
How are clients getting strings from oracle dates without a to_char?
On Fri, Mar 26, 2021, 3:21 PM Tim <timfosho@gmail.com> wrote:
> Yes anything that involves management at a table level is not practical
> due to the size of our database and limited amount of resources (me). I was
> not aware of the orafce extension and will keep it in mind.
>
> Tom, we currently are on EDB AS 12 but its becoming cost inefficient to
> stay with EDB due to our scale, since their pricing reflects the size of
> the production VMs that are used. So while this migration might be a pain
> from the application side.. long run it would be cheaper.
>
> Jonah, I'm assuming a patch would only apply per Postgres version... so
> any future PG version upgrade would require a new patch? While I'm sure
> technically its doable.. long run it would be more practical to just move
> away from these cursed Oracle dates.
>
> Thanks for your help everyone!
>
> On Fri, Mar 26, 2021 at 1:27 PM Jonah H. Harris <jonah.harris@gmail.com>
> wrote:
>
>> On Fri, Mar 26, 2021 at 12:55 PM legrand legrand <
>> legrand_legrand@hotmail.com> wrote:
>>
>>> Isn’t this alteady proposed in extension oracfe ?
>>> https://github.com/orafce/orafce
>>
>>
>> I don't believe this can be done implicitly, across the entire server, by
>> default via an extension. It could be done on a table-by-table or
>> view-level basis using custom formatting (via a function), which it seems
>> Tim would like to avoid.
>>
>> --
>> Jonah H. Harris
>>
>>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Creating redwood datestyle in Postgres 12
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-25 12:38 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 09:16 ` Re: Creating redwood datestyle in Postgres 12 Paul Smith <paul@pscs.co.uk>
2021-03-26 12:24 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 12:35 ` Re: Creating redwood datestyle in Postgres 12 Bruce Momjian <bruce@momjian.us>
2021-03-26 16:55 ` Re: Creating redwood datestyle in Postgres 12 legrand legrand <legrand_legrand@hotmail.com>
2021-03-26 17:27 ` Re: Creating redwood datestyle in Postgres 12 Jonah H. Harris <jonah.harris@gmail.com>
2021-03-26 20:21 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-27 00:19 ` Re: Creating redwood datestyle in Postgres 12 jim schmidt <txherper@gmail.com>
@ 2021-03-27 02:15 ` Tim <timfosho@gmail.com>
2021-03-29 07:42 ` Re: Creating redwood datestyle in Postgres 12 Vivek Anandh <ganiba22664@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Tim @ 2021-03-27 02:15 UTC (permalink / raw)
To: jim schmidt <txherper@gmail.com>; +Cc: Jonah H. Harris <jonah.harris@gmail.com>; legrand legrand <legrand_legrand@hotmail.com>; pgsql-admin
Jim, all that is true but when it comes to application code you never know
what it'll be doing with the date. It can be getting casted, used as a
string, inserted as a string and casted. I doubt the devs even know all the
ways. Ive had to deal with switching from ISO to SQL and even that was
painful and required retroactive data fixes and code patches
On Fri, Mar 26, 2021 at 8:19 PM jim schmidt <txherper@gmail.com> wrote:
> I am confused, don't dates that traverse the network get transported in
> canonical format and the client format the date?
>
>
> This obviously is not the case when one applies a to_char function but in
> jdbc the canonical format is translated to a java.sql.Date and formatting
> is a client responsibility. Same applies with pro*c.
>
> I would think if the formatting is done by the client that a server patch
> would do nothing.
>
> Also creating to_char views and programmatically is a trivial operation.
>
> How are clients getting strings from oracle dates without a to_char?
>
>
>
> On Fri, Mar 26, 2021, 3:21 PM Tim <timfosho@gmail.com> wrote:
>
>> Yes anything that involves management at a table level is not practical
>> due to the size of our database and limited amount of resources (me). I was
>> not aware of the orafce extension and will keep it in mind.
>>
>> Tom, we currently are on EDB AS 12 but its becoming cost inefficient to
>> stay with EDB due to our scale, since their pricing reflects the size of
>> the production VMs that are used. So while this migration might be a pain
>> from the application side.. long run it would be cheaper.
>>
>> Jonah, I'm assuming a patch would only apply per Postgres version... so
>> any future PG version upgrade would require a new patch? While I'm sure
>> technically its doable.. long run it would be more practical to just move
>> away from these cursed Oracle dates.
>>
>> Thanks for your help everyone!
>>
>> On Fri, Mar 26, 2021 at 1:27 PM Jonah H. Harris <jonah.harris@gmail.com>
>> wrote:
>>
>>> On Fri, Mar 26, 2021 at 12:55 PM legrand legrand <
>>> legrand_legrand@hotmail.com> wrote:
>>>
>>>> Isn’t this alteady proposed in extension oracfe ?
>>>> https://github.com/orafce/orafce
>>>
>>>
>>> I don't believe this can be done implicitly, across the entire server,
>>> by default via an extension. It could be done on a table-by-table or
>>> view-level basis using custom formatting (via a function), which it seems
>>> Tim would like to avoid.
>>>
>>> --
>>> Jonah H. Harris
>>>
>>>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Creating redwood datestyle in Postgres 12
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Re: Creating redwood datestyle in Postgres 12 Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-25 12:38 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 09:16 ` Re: Creating redwood datestyle in Postgres 12 Paul Smith <paul@pscs.co.uk>
2021-03-26 12:24 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-26 12:35 ` Re: Creating redwood datestyle in Postgres 12 Bruce Momjian <bruce@momjian.us>
2021-03-26 16:55 ` Re: Creating redwood datestyle in Postgres 12 legrand legrand <legrand_legrand@hotmail.com>
2021-03-26 17:27 ` Re: Creating redwood datestyle in Postgres 12 Jonah H. Harris <jonah.harris@gmail.com>
2021-03-26 20:21 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-27 00:19 ` Re: Creating redwood datestyle in Postgres 12 jim schmidt <txherper@gmail.com>
2021-03-27 02:15 ` Re: Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
@ 2021-03-29 07:42 ` Vivek Anandh <ganiba22664@gmail.com>
0 siblings, 0 replies; 17+ messages in thread
From: Vivek Anandh @ 2021-03-29 07:42 UTC (permalink / raw)
To: Tim <timfosho@gmail.com>; +Cc: Jonah H. Harris <jonah.harris@gmail.com>; jim schmidt <txherper@gmail.com>; legrand legrand <legrand_legrand@hotmail.com>; pgsql-admin
How to get a list of PostGreS developers and customers to open a beta
version of a fully Homomorphic encrypted version of PostGreS. Making
community version to carry all the bells and whistles that will make
PostGreSQL worlds first and only database which is truly Homomorphic.
Allowing queries and applications to work on top of encrypted database
(incl hybrid structure) without a need to decrypt at anytime including
while processing.
Cheers
Vivek
On Sat, 27 Mar 2021 at 10:15 AM, Tim <timfosho@gmail.com> wrote:
> Jim, all that is true but when it comes to application code you never know
> what it'll be doing with the date. It can be getting casted, used as a
> string, inserted as a string and casted. I doubt the devs even know all the
> ways. Ive had to deal with switching from ISO to SQL and even that was
> painful and required retroactive data fixes and code patches
>
>
>
>
>
>
>
> On Fri, Mar 26, 2021 at 8:19 PM jim schmidt <txherper@gmail.com> wrote:
>
>> I am confused, don't dates that traverse the network get transported in
>> canonical format and the client format the date?
>>
>>
>> This obviously is not the case when one applies a to_char function but in
>> jdbc the canonical format is translated to a java.sql.Date and formatting
>> is a client responsibility. Same applies with pro*c.
>>
>> I would think if the formatting is done by the client that a server patch
>> would do nothing.
>>
>> Also creating to_char views and programmatically is a trivial operation.
>>
>> How are clients getting strings from oracle dates without a to_char?
>>
>>
>>
>> On Fri, Mar 26, 2021, 3:21 PM Tim <timfosho@gmail.com> wrote:
>>
>>> Yes anything that involves management at a table level is not practical
>>> due to the size of our database and limited amount of resources (me). I was
>>> not aware of the orafce extension and will keep it in mind.
>>>
>>> Tom, we currently are on EDB AS 12 but its becoming cost inefficient to
>>> stay with EDB due to our scale, since their pricing reflects the size of
>>> the production VMs that are used. So while this migration might be a pain
>>> from the application side.. long run it would be cheaper.
>>>
>>> Jonah, I'm assuming a patch would only apply per Postgres version... so
>>> any future PG version upgrade would require a new patch? While I'm sure
>>> technically its doable.. long run it would be more practical to just move
>>> away from these cursed Oracle dates.
>>>
>>> Thanks for your help everyone!
>>>
>>> On Fri, Mar 26, 2021 at 1:27 PM Jonah H. Harris <jonah.harris@gmail.com>
>>> wrote:
>>>
>>>> On Fri, Mar 26, 2021 at 12:55 PM legrand legrand <
>>>> legrand_legrand@hotmail.com> wrote:
>>>>
>>>>> Isn’t this alteady proposed in extension oracfe ?
>>>>> https://github.com/orafce/orafce
>>>>
>>>>
>>>> I don't believe this can be done implicitly, across the entire server,
>>>> by default via an extension. It could be done on a table-by-table or
>>>> view-level basis using custom formatting (via a function), which it seems
>>>> Tim would like to avoid.
>>>>
>>>> --
>>>> Jonah H. Harris
>>>>
>>>>
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2021-03-29 07:42 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-24 23:18 Creating redwood datestyle in Postgres 12 Tim <timfosho@gmail.com>
2021-03-25 04:06 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-25 12:38 ` Tim <timfosho@gmail.com>
2021-03-25 17:02 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-26 09:16 ` Paul Smith <paul@pscs.co.uk>
2021-03-26 12:24 ` Tim <timfosho@gmail.com>
2021-03-26 12:35 ` Bruce Momjian <bruce@momjian.us>
2021-03-26 13:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-03-26 15:27 ` Jonah H. Harris <jonah.harris@gmail.com>
2021-03-26 16:55 ` legrand legrand <legrand_legrand@hotmail.com>
2021-03-26 17:27 ` Jonah H. Harris <jonah.harris@gmail.com>
2021-03-26 20:21 ` Tim <timfosho@gmail.com>
2021-03-26 20:23 ` Bruce Momjian <bruce@momjian.us>
2021-03-26 22:34 ` Jonah H. Harris <jonah.harris@gmail.com>
2021-03-27 00:19 ` jim schmidt <txherper@gmail.com>
2021-03-27 02:15 ` Tim <timfosho@gmail.com>
2021-03-29 07:42 ` Vivek Anandh <ganiba22664@gmail.com>
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