Re: [GENERAL] Money casting too liberal?

On 03/04/13 07:16, John R Pierce wrote: On 4/2/2013 12:50 AM, Gavin Flower wrote: In the bad old days when I was a COBOL programmer we always stored money in the COBOL equivalent of an integer (numeric without a fractional part) to avoid round off, but we displayed with a decimal point to digi

Re: [GENERAL] Money casting too liberal?

On 4/2/2013 12:50 AM, Gavin Flower wrote: In the bad old days when I was a COBOL programmer we always stored money in the COBOL equivalent of an integer (numeric without a fractional part) to avoid round off, but we displayed with a decimal point to digits to the left. So storing as an integer

Re: [GENERAL] Money casting too liberal?

On 30/03/13 08:36, Michael Nolan wrote: On 3/27/13, Steve Crawford wrote: Somewhat more worrisome is the fact that it automatically rounds input (away from zero) to fit. select '123.456789'::money; money - $123.46 So does casting to an integer: select 1.25::integer ; int4 --

Re: [GENERAL] Money casting too liberal?

On 30/03/13 11:30, Gavan Schneider wrote: On 29/3/13 at 3:32 AM, D'Arcy J.M. Cain wrote: On Fri, 29 Mar 2013 11:46:40 -0400 Tom Lane wrote: Well, this has been discussed before, and the majority view every time has been that MONEY is a legacy thing that most people would rather rip out than si

Re: [GENERAL] Money casting too liberal?

On 30/03/13 04:08, Gavan Schneider wrote: Some thoughts. The current MONEY type might be considered akin to ASCII. Perfect for a base US centric accounting system where there are cents and dollars and no need to carry smaller fractions. As discussed, there are some details that could be refin

Re: [GENERAL] Money casting too liberal?

On 29/03/13 12:39, Jasen Betts wrote: On 2013-03-28, Gavin Flower wrote: Hmm... This should optionally apply to time. e.g. time_i_got_up_in_the_morning should reflect the time zone where I got up - if I got up at 8am NZ time then this should be displayed, not 12pm (12 noon) to someone in Los A

Re: [GENERAL] Money casting too liberal?

On Sat, 2013-03-30 at 09:52 -0400, D'Arcy J.M. Cain wrote: > That's why I suggested that operations between money(2) and money(3) > should raise an error. Treat them as distinct types. I don't think typmod is currently powerful enough to do that. It's lost in many different types of expressions.

Re: [GENERAL] Money casting too liberal?

On 31/3/13 at 5:20 AM, D'Arcy J.M. Cain wrote: On Sun, 31 Mar 2013 21:57:49 +1100 Gavan Schneider wrote: On 30/3/13 at 12:58 AM, D'Arcy J.M. Cain wrote: That seems extreme. What use case would there ever be for making a column always debit or always credit? I have a G/L system and most money

Re: [GENERAL] Money casting too liberal?

chneider Sent: 31/03/2013 12:59 To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Money casting too liberal? On 30/3/13 at 12:58 AM, D'Arcy J.M. Cain wrote: >On Sat, 30 Mar 2013 12:04:21 +1100 Gavan Schneider wrote: >>No MONEY column would be complete without the ability to >

Re: [GENERAL] Money casting too liberal?

Søndag 31. mars 2013 18.45.10 skrev ajmcello : > unsubscribe > > On Sun, Mar 31, 2013 at 3:31 AM, Gavan Schneider wrote: > > On 30/3/13 at 11:09 PM, D'Arcy J.M. Cain wrote: > > I am formulating Cain's Law. Something like "If a discussion lasts > > > >> long enough, someone will mention Godwi

Re: [GENERAL] Money casting too liberal?

On 31/03/2013 17:45, ajmcello wrote: > unsubscribe Hi there, Instructions for unsubscribing are in the footer of every email sent from the list: > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org > ) > To make changes to

Re: [GENERAL] Money casting too liberal?

unsubscribe On Sun, Mar 31, 2013 at 3:31 AM, Gavan Schneider wrote: > On 30/3/13 at 11:09 PM, D'Arcy J.M. Cain wrote: > > I am formulating Cain's Law. Something like "If a discussion lasts >> long enough, someone will mention Godwin's Law." >> > > +1 > > More formally: > As an online disc

Re: [GENERAL] Money casting too liberal?

On 31/03/13 21:57, Gavan Schneider wrote: > On 30/3/13 at 12:58 AM, D'Arcy J.M. Cain wrote: > Basically if MONEY is to be a useful tool it should really handle money > matters in a way that makes accountants happy. If it can't do that then > nobody is going to bother using it for serious work since

Re: [GENERAL] Money casting too liberal?

On 30/3/13 at 12:58 AM, D'Arcy J.M. Cain wrote: On Sat, 30 Mar 2013 12:04:21 +1100 Gavan Schneider wrote: No MONEY column would be complete without the ability to specify whether it is normally DEBIT or CREDIT (or in my preferred case That seems extreme. What use case would there ever be fo

Re: [GENERAL] Money casting too liberal?

On 30/3/13 at 11:09 PM, D'Arcy J.M. Cain wrote: I am formulating Cain's Law. Something like "If a discussion lasts long enough, someone will mention Godwin's Law." +1 More formally: As an online discussion grows longer, the probability of Godwin's Law being mentioned approaches one

Re: [GENERAL] Money casting too liberal?

unsubscribe On Wed, Mar 27, 2013 at 3:12 PM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > In contrast to certain other open-source databases, PostgreSQL leans > toward protecting data from surprises and erroneous input, i.e. rejecting a > date of 2013-02-31 instead of arbitrarily as

Re: [GENERAL] Money casting too liberal?

Martijn van Oosterhout developed "tagged types" back in 2005, looks like it went nowhere. You can search for it, it was pretty interesting. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing

Re: [GENERAL] Money casting too liberal?

On 2013-03-29, Gavan Schneider wrote: > Some thoughts. > > The current MONEY type might be considered akin to ASCII. > Perfect for a base US centric accounting system where there are > cents and dollars and no need to carry smaller fractions. As > discussed, there are some details that could be

Re: [GENERAL] Money casting too liberal?

On Sat, 30 Mar 2013 12:04:21 +1100 Gavan Schneider wrote: > No MONEY column would be complete without the ability to specify > whether it is normally DEBIT or CREDIT (or in my preferred case That seems extreme. What use case would there ever be for making a column always debit or always credit

Re: [GENERAL] Money casting too liberal?

On Fri, 29 Mar 2013 17:23:50 -0700 Jeff Davis wrote: > Why not have various rounding functions that do exactly what you want? > Then you can use them anywhere you want in an expression. Perhaps but the languages that we use all have the capability to manage this and we will probably never cover a

Re: [GENERAL] Money casting too liberal?

On Sat, 30 Mar 2013 20:36:22 +1100 Julian wrote: > The comparisons with timezones ends when it comes to exchange rates. The only comparison I made with time and time zones was in how the column type can be refined when it is created. > With a current WIP. I'm starting to think that numeric is pr

Re: [GENERAL] Money casting too liberal?

On Fri, 29 Mar 2013 23:32:20 +1100 Gavan Schneider wrote: > Is there a Godwin's law I am formulating Cain's Law. Something like "If a discussion lasts long enough, someone will mention Godwin's Law." :-) -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/

Re: [GENERAL] Money casting too liberal?

"Interesting discussion. The comparisons with timezones ends when it comes to exchange rates. The rate at the time of transaction has to the stored (somewhere) associated with the base value. Timezones are rather fixed." +1 No way can be solved just by type On Saturday, March 30, 2013, Jul

Re: [GENERAL] Money casting too liberal?

On 29/03/13 23:32, Gavan Schneider wrote: > Some people wrote: > >> ... Hmm... This should optionally apply to time. >> ... for anything that really matters, I'll work with UTC. >> > Is there a Godwin's law > equivalent for when our conversations end up

Re: [GENERAL] Money casting too liberal?

On 30/3/13 at 9:30 AM, I wrote: I have sketched something of a notation for MONEY columns along these lines: amt_received MONEY (CURRENCY-- e.g., 'USD' 'AUD' 'YEN' ... [,SCALE -- default as per currency, e.g. USD 2 decimals -- but could be used to see money in bigger units -- such a

Re: [GENERAL] Money casting too liberal?

On Fri, 2013-03-29 at 16:30 -0400, D'Arcy J.M. Cain wrote: > How would this be an issue? If you are assigning a literal to a column > then that's no issue. Otherwise, a literal is simply a value that can > be cast depending on the situation. The money type is no different in > that regard. > >

Re: [GENERAL] Money casting too liberal?

On 29/3/13 at 3:32 AM, D'Arcy J.M. Cain wrote: On Fri, 29 Mar 2013 11:46:40 -0400 Tom Lane wrote: Well, this has been discussed before, and the majority view every time has been that MONEY is a legacy thing that most people would rather rip out than sink a large amount of additional effort into

Re: [GENERAL] Money casting too liberal?

On Fri, 29 Mar 2013 12:02:49 -0700 Jeff Davis wrote: > On Thu, 2013-03-28 at 23:43 +1100, Gavan Schneider wrote: > > If the money type is meant to be serious then these > > conventions need to be followed/settable on a column by column > > basis. > > I don't like the idea of tying the semantics

Re: [GENERAL] Money casting too liberal?

On 3/27/13, Steve Crawford wrote: > Somewhat more worrisome is the fact that it automatically rounds input > (away from zero) to fit. > > select '123.456789'::money; >money > - > $123.46 So does casting to an integer: select 1.25::integer ; int4 1 And then there's this:

Re: [GENERAL] Money casting too liberal?

On Thu, 2013-03-28 at 23:43 +1100, Gavan Schneider wrote: > If the money type is meant to be serious then these > conventions need to be followed/settable on a column by column > basis. I don't like the idea of tying the semantics to a column. That leaves out values that aren't stored in a colum

Re: [GENERAL] Money casting too liberal?

On 28 March 2013 13:52, Shaun Thomas wrote: > On 03/28/2013 07:43 AM, Gavan Schneider wrote: > > Personally I have ignored the money type in favour of numeric. Money >> seemed to do too much behind the scenes for my taste, but, that's me >> being lazy as well, I haven't spend much time trying to

Re: [GENERAL] Money casting too liberal?

On Fri, 29 Mar 2013 11:46:40 -0400 Tom Lane wrote: > Well, this has been discussed before, and the majority view every > time has been that MONEY is a legacy thing that most people would > rather rip out than sink a large amount of additional effort into. > It has some use-cases but they are narro

Re: [GENERAL] Money casting too liberal?

Gavan Schneider writes: > Therefore the discussion is really about the desired role for > the MONEY type. Should it be refined in its current dallar and > cents mode? or, be promoted to a more universal role (akin to a > shift from ASCII to UTF)? Well, this has been discussed before, and the

Re: [GENERAL] Money casting too liberal?

Some thoughts. The current MONEY type might be considered akin to ASCII. Perfect for a base US centric accounting system where there are cents and dollars and no need to carry smaller fractions. As discussed, there are some details that could be refined. When it comes to this type being used

Re: [GENERAL] Money casting too liberal?

Some people wrote: ... Hmm... This should optionally apply to time. ... for anything that really matters, I'll work with UTC. Is there a Godwin's law equivalent for when our conversations end up with timezones getting mentioned? :) Regards Gavan S

Re: [GENERAL] Money casting too liberal?

Steve Crawford wrote > > select ',123,456,,7,8.1,0,9'::money; > money > > $12,345,678.11 As an end-user it would seem since a comma (or whatever the locale defines as a group separator) carries no significant information - it is purely aesthetic - that ignoring all commas

Re: [GENERAL] Money casting too liberal?

On Fri, Mar 29, 2013 at 10:39 AM, Jasen Betts wrote: > how confusing is 'EST' ? > worse than this: > > set datestyle to 'sql,dmy'; > set time zone 'Australia/Brisbane'; > select '20130101T00Z'::timestamptz; > set time zone 'Australia/Sydney'; > select '20130101T00Z'::timestamptz; > set tim

Re: [GENERAL] Money casting too liberal?

On 2013-03-28, Gavin Flower wrote: > > Hmm... This should optionally apply to time. e.g. > time_i_got_up_in_the_morning should reflect the time zone where I got up > - if I got up at 8am NZ time then this should be displayed, not 12pm (12 > noon) to someone in Los Angeles or 3am in Tokyo! (hav

Re: [GENERAL] Money casting too liberal?

On 29/03/13 10:13, D'Arcy J.M. Cain wrote: On 28 Mar 2013 20:50:42 GMT Jasen Betts wrote: it actually does that, if you have the locale installed you can set LC_MONETARY to Japan and get no decimals and a Yen symbol or to UAE and get three decimals and their currency symbol. Must have been a

Re: [GENERAL] Money casting too liberal?

On 03/28/2013 04:36 PM, John R Pierce wrote: or to another extreme, part of the data, such that different rows could have different monetary units.(eg, money is implemented as a pair (currency,amount).eeek, then you'd need exchange rate tables and such. hahahahaha, what a nightmare.

Re: [GENERAL] Money casting too liberal?

On 3/28/2013 2:13 PM, D'Arcy J.M. Cain wrote: I would have rather made that part of the column definition similar to how we create timestamps with or without timezones. If a column is tracking Yen it should always be Yen. Y10,000 should never display as $100.00 just because the locale changes.

Re: [GENERAL] Money casting too liberal?

On 28 Mar 2013 20:50:42 GMT Jasen Betts wrote: > it actually does that, if you have the locale installed you can set > LC_MONETARY to Japan and get no decimals and a Yen symbol > or to UAE and get three decimals and their currency symbol. Must have been added by someone else after I worked on

Re: [GENERAL] Money casting too liberal?

On 2013-03-28, D'Arcy J.M. Cain wrote: > > I would like to see the type handle other situations such as foreign > (to me) currency, etc. I suppose a positional parameter and a currency > string setting would handle most of those issues. Technically, the > money type is a cents type. Everything

Re: [GENERAL] Money casting too liberal?

On 29/03/13 02:28, D'Arcy J.M. Cain wrote: On Thu, 28 Mar 2013 23:43:23 +1100 Gavan Schneider wrote: But it appears that the philosophy does not extend to the "money" type. ... As the original author of the money type I guess I should weigh in. select ',123,456,,7,8.1,0,9'::money; money

Re: [GENERAL] Money casting too liberal?

> On 27/3/13 at 9:12 AM, Steve Crawford wrote: >> Thoughts? Is this the "no surprises" way that money input should behave? I took a quick look at cash_in(), which is what's being complained of here (not really casting). There are several things that seem like they could possibly stand to be tight

Re: [GENERAL] Money casting too liberal?

On 03/28/2013 07:43 AM, Gavan Schneider wrote: Personally I have ignored the money type in favour of numeric. Money seemed to do too much behind the scenes for my taste, but, that's me being lazy as well, I haven't spend much time trying to understand its features. You're not the only one. In

Re: [GENERAL] Money casting too liberal?

On Thu, 28 Mar 2013 23:43:23 +1100 Gavan Schneider wrote: > >But it appears that the philosophy does not extend to the "money" > >type. ... As the original author of the money type I guess I should weigh in. > >select ',123,456,,7,8.1,0,9'::money; > >money > > > >$12,345,678.11

Re: [GENERAL] Money casting too liberal?

On 27/3/13 at 9:12 AM, Steve Crawford wrote: In contrast to certain other open-source databases, PostgreSQL leans toward protecting data from surprises ... And long may this continue. But it appears that the philosophy does not extend to the "money" type. ... select ',123,456,,7,8.1,0,9'::m

Re: [GENERAL] Money in numeric field

El día 20 de abril de 2012 05:51, Albe Laurenz escribió: > Martín Marqués wrote: >> I have a question involving money data stored in a numeric(9,2) field, >> and posible errors with there manipulation. >> >> in short, the table has these columns: >> >> store: int >> amount: int2 >> cost: numeric(9

Re: [GENERAL] Money in numeric field

Martín Marqués wrote: > I have a question involving money data stored in a numeric(9,2) field, > and posible errors with there manipulation. > > in short, the table has these columns: > > store: int > amount: int2 > cost: numeric(9,2) > > What I need to find is the total amount of money spent in

Re: [GENERAL] "money" binary representation

I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the Huh... You mean 8 bytes, right? -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-general mailing list (pgsql-

Re: [GENERAL] "money" binary representation

On Mon, Nov 16, 2009 at 3:02 AM, Jasen Betts wrote: > On 2009-11-16, Konstantin Izmailov wrote: >> --000e0cd5d09230ff7d04787526aa >> Content-Type: text/plain; charset=ISO-8859-1 >> >> I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The >> function returns 9 bytes, smth like

Re: [GENERAL] "money" binary representation

On 2009-11-16, Konstantin Izmailov wrote: > --000e0cd5d09230ff7d04787526aa > Content-Type: text/plain; charset=ISO-8859-1 > > I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The > function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the > value '$50.2'. I coul

Re: [GENERAL] "money" binary representation

Right, the value is '$51.20'! Now I understand how to interpret the bytes - thank you! I had to work with an existing database and I do not know why they still use "money" fields. On Sun, Nov 15, 2009 at 9:38 PM, John R Pierce wrote: > Konstantin Izmailov wrote: > >> I'm trying to read "money" f

Re: [GENERAL] "money" binary representation

Konstantin Izmailov writes: > I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The > function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the > value '$50.2'. I could not find description anywhere on how to convert the > binary data into, for example, a double

Re: [GENERAL] "money" binary representation

Konstantin Izmailov wrote: I'm trying to read "money" field using PQgetvalue (PostgreSQL 8.3.7). The function returns 9 bytes, smth like 0h 0h 0h 0h 0h 0h 14h 0h 0h, for the value '$50.2'. I could not find description anywhere on how to convert the binary data into, for example, a double precis

Re: [GENERAL] Money data type - Deprecated?

On Mon, Jun 16, 2008 at 11:10:09AM -0500, Decibel! wrote: > >I personally think that it really belongs in contrib as an example of > >adding a new type...with all the money bits stripped off, as a > >strictly fixed precision integer. > > I think what it really needs is currency information so that

Re: [GENERAL] Money data type - Deprecated?

On May 27, 2008, at 8:29 AM, Merlin Moncure wrote: On Mon, May 26, 2008 at 4:05 PM, Andrew Sullivan <[EMAIL PROTECTED]> wrote: On Mon, May 26, 2008 at 03:46:39PM -0400, Justin wrote: Thats a good Question is the Money type ever going to die i believe it was deprecated several versions ago?

Re: [GENERAL] money type depreciated?

On Tue, Aug 01, 2006 at 10:37:35PM +, Roman Neuhauser wrote: > # [EMAIL PROTECTED] / 2006-08-01 10:18:45 -0700: > > I read in the documentation that the money type is depreciated. It > > says to use the to_char function and NUMERIC/decimal instead. Why was > > the money type depreciated when

Re: [GENERAL] money type depreciated?

# [EMAIL PROTECTED] / 2006-08-01 10:18:45 -0700: > I read in the documentation that the money type is depreciated. It > says to use the to_char function and NUMERIC/decimal instead. Why was > the money type depreciated when it was so useful? How would be the > best way to use to_char and numeri

Re: [GENERAL] Money data type in PostgreSQL?

Graham Leggett wrote: Jan Wieck wrote: You won't get any rounding errors in NUMERIC either. What people should be concerned of is to find an arbitrary precision package for the frontend programming language they're using. What is the definition of a numeric number? I understand (from studying n

Re: [GENERAL] Money data type in PostgreSQL?

Graham Leggett wrote: Christopher Browne wrote: But to get the pennies right on a $10,000 USD transaction converted into GBP (UK Pounds), you need all the official precision that there is. And if your calculation is off by 4 cents, some of those accounting folk are liable to thrash you merciless

Re: [GENERAL] Money data type in PostgreSQL?

Christopher Browne wrote: But to get the pennies right on a $10,000 USD transaction converted into GBP (UK Pounds), you need all the official precision that there is. And if your calculation is off by 4 cents, some of those accounting folk are liable to thrash you mercilessly over it. If you get

Re: [GENERAL] Money data type in PostgreSQL?

Martha Stewart called it a Good Thing when [EMAIL PROTECTED] ("Claudio Lapidus") wrote: > With regard to precision, it is common in certain applications the need to > handle very small amounts, especially when used as factors of a larger > calculation. I've even seen once some rates defined in hund

Re: [GENERAL] Money data type in PostgreSQL?

Oliver Elphick wrote: You should not regard amounts in different currencies as equivalent. You cannot add Euros to dollars and get a meaningful figure; so they should not be in the same column. I plan to store amount in a column (NUMERIC) and currency id in another (CHAR(3)). Plus another column

Re: [GENERAL] Money data type in PostgreSQL?

On Wed, 2003-12-03 at 07:02, David Garamond wrote: > What do people recommend for storing money amounts? I've seen people use > NUMERIC(18,3) and other use NUMERIC(18,4). Which one is more appropriate > and why? This is considering various existing currencies, some having > low rates (like IDR,

Re: [GENERAL] Money...

Hi Christian, On Sat, 10 Mar 2001, Christian Marschalek wrote: > Since Oracle's prices are somewhat huge, I'm forced to switch to PostgreSQL! > I would have used it from the start, if I just knew of it's existance ;o) Oracle's prices are through the roof. Its crazy =) > > Now, I wonder if Pos

Re: [GENERAL] Money...

From: "Christian Marschalek" <[EMAIL PROTECTED]> > Since Oracle's prices are somewhat huge, I'm forced to switch to PostgreSQL! > I would have used it from the start, if I just knew of it's existance ;o) One place Oracle beats PostgreSQL hands down is the size of its marketing budget. > Now, I

Re: [GENERAL] Money

Bob Dusek wrote: > > Hey there, > > I had some similar problems recently, try this > > select someid, somemoneyfield * 2::float as product from table; > > You cannot multiply money * money, apparently. But, money * float seems > to work fine. Same goes with division: > > select someid, some

Re: [GENERAL] Money

Hey there, I had some similar problems recently, try this select someid, somemoneyfield * 2::float as product from table; You cannot multiply money * money, apparently. But, money * float seems to work fine. Same goes with division: select someid, somemoneyfield / 2::float as quotient from t