From: | Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> |
---|---|
To: | Tony Theodore <tony(dot)theodore(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Using regoper type with OPERATOR() |
Date: | 2011-10-09 21:03:52 |
Message-ID: | 4E920C38.9090306@archidevsys.co.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 08/10/11 19:21, Tony Theodore wrote:
> On 7 October 2011 06:33, Gavin Flower<GavinFlower(at)archidevsys(dot)co(dot)nz> wrote:
>> Glad to be of help!
>>
>> There is often a tradeoff between flexibility and performance.
>>
>> What you tried to do looks pretty neat.
>>
>> Would writing something in C give you sufficient flexibility with reasonable
>> performance?
> Possibly, but I wouldn't know where to start. I just did some more
> testing, and the most performant solution is to just have both columns
> (fraction and amount) default them to 1 and 0 respectively, then just
> calculate (price * fraction + amount).
I think this is a better aproach it provides greater flexibility and
eliminates the case statement - so I suspect it will be slightly faster.
>
>> However, in a production system, and in an environment where most people do
>> not have a range of skills in depth, it is better to keep things simple - to
>> ease ongoing maintenance. Sometimes super smart code is a liability, as
>> mere mortals can not maintain it. I have been guilty of this crime!
>>
>> I guess a good rule of thumb, is imagine that you are called back in 2 years
>> to fix, or modify your code - how would you feel: still proud of what you
>> did, or wonder what you were thinking at the time (or both!)?
>>
>> Somes a bit of complexity is necessar, and can save a lot of code, or imply
>> be the most practical way of doing something.
> I was trying to build some flexibility in so that I wouldn't need to
> revisit this in the future :) Down the track, I'll investigate
> operator/function lookups further, but I'll keep it simple for the
> time being.
>
> BTW, is novice the right list for questions like these?
>
I think so, but the pgsql-sql list would probably not be appropriate as
you are not asking a trivial question - on balance, I feel this list is
best. IMHO How is that for a definitive answer! :-)
I first started useing databases about 20 years ago, and came across pg
about 10 years ago. I find reading the pg mailing lists very useful for
learning new things. Sometimes I solve problems better than others, but
I also often find other people's answers provide more practically
elegant ways of doing things than I could have come up with. Other
times I've found my understanding not as as good as I had thought - like
not appreciating the need to use timestamps with timezone (timestamptz
is a pg short form). Having lots of experience is great, but things
keep changing and it is important not to get complacent!
>> Note that one of the points I was trying to make is to avoid float type data
>> types for money. In COBOL we used integers to hold the number of cents, so
>> add&subtract operations were not subject to rounding, in pg you can use the
>> money type.
> Thanks for the tip, this is mostly an analysis database, so rounding
> won't be an issue.
>
> Cheers,
>
> Tony
You're okay then. Float is probably slightly nore efficient than the
money type. Though the money type is probably better from the semantic
point of view.
Cheers,
Gavin
From | Date | Subject | |
---|---|---|---|
Next Message | Gavin Flower | 2011-10-09 21:22:54 | Re: Using regoper type with OPERATOR() |
Previous Message | Tony Theodore | 2011-10-08 06:21:12 | Re: Using regoper type with OPERATOR() |