pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedformat integer
5+ messages / 4 participants
[nested] [flat]
* format integer
@ 2021-01-24 14:57 ml@ft-c.de
0 siblings, 2 replies; 5+ messages in thread
From: ml@ft-c.de @ 2021-01-24 14:57 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
Hello,
I need a integer format with different decimal places
The integer should have 6 diggits. (or 7 or 8)
Example (6-diggits)
input -> output
123456.789 -> 123456
1234.56789 -> 1234.56
12.3456789 -> 12.3456
but when it is more then 10^6 then
12345678.9 -> 12345678
Is there a pg function for this task?
Franz
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: format integer
@ 2021-01-24 16:06 David G. Johnston <david.g.johnston@gmail.com>
parent: ml@ft-c.de
1 sibling, 2 replies; 5+ messages in thread
From: David G. Johnston @ 2021-01-24 16:06 UTC (permalink / raw)
To: ml@ft-c.de <ml@ft-c.de>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
On Sunday, January 24, 2021, <ml@ft-c.de> wrote:
>
>
> I need a integer format with different decimal places
> The integer should have 6 diggits. (or 7 or 8)
>
No, there isn’t a function to apply this convoluted formatting rule to
non-integer numbers. You will need to write your own.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: format integer
@ 2021-01-24 16:37 Steve Midgley <science@misuse.org>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Steve Midgley @ 2021-01-24 16:37 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: ml@ft-c.de <ml@ft-c.de>; pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
On Sun, Jan 24, 2021 at 8:06 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
>
> On Sunday, January 24, 2021, <ml@ft-c.de> wrote:
>>
>>
>> I need a integer format with different decimal places
>> The integer should have 6 diggits. (or 7 or 8)
>>
>
>
> No, there isn’t a function to apply this convoluted formatting rule to
> non-integer numbers. You will need to write your own.
>
> David J.
>
> I've had to deal with something similar in the past, strangely. My
solution was to convert the numeric formats to strings, and process there
to get the right rule set regarding number of digits (and preserving all
digits left of the period where required). Then convert back to your
original numeric format. The one thing I'd mention as an edge case is that
not all number systems use period as the significant digit delimiter, so be
sure you guarantee the number formatting when you convert to string is what
you expect when using this approach. I'd guess you could also solve this
with logic and math (floor/ceiling/modulus, etc) but I found that using
strings and regex was much easier for me.
Steve
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: format integer
@ 2021-01-24 16:48 Torge Kummerow <tk@panaccess.com>
parent: ml@ft-c.de
1 sibling, 0 replies; 5+ messages in thread
From: Torge Kummerow @ 2021-01-24 16:48 UTC (permalink / raw)
To: ml@ft-c.de; pgsql-sql@lists.postgresql.org
Well strange requirement.
Guess you could do it with
case when x < 10 then ... else when x < 100 then ....
Don't think you'll find anything native.
Am 24. Januar 2021 15:57:02 MEZ schrieb ml@ft-c.de:
>Hello,
>
>I need a integer format with different decimal places
>The integer should have 6 diggits. (or 7 or 8)
>
>Example (6-diggits)
>input -> output
>123456.789 -> 123456
>1234.56789 -> 1234.56
>12.3456789 -> 12.3456
>
>but when it is more then 10^6 then
>12345678.9 -> 12345678
>
>Is there a pg function for this task?
>
>Franz
--
Diese Nachricht wurde von meinem Android-Gerät mit K-9 Mail gesendet.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: format integer
@ 2021-01-24 17:35 ml@ft-c.de
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: ml@ft-c.de @ 2021-01-24 17:35 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
On Sun, 2021-01-24 at 09:06 -0700, David G. Johnston wrote:
>
> On Sunday, January 24, 2021, <ml@ft-c.de> wrote:
> >
> > I need a integer format with different decimal places
> > The integer should have 6 diggits. (or 7 or 8)
> >
> >
>
> No, there isn’t a function to apply this convoluted formatting rule
> to non-integer numbers. You will need to write your own.
>
> David J.
>
My idea is
SELECT round(1.2345678912, 5 - least(log(1.2345678912)::int,5) );
SELECT round(12.345678912, 5 - least(log(12.345678912)::int,5) );
SELECT round(123.45678912, 5 - least(log(123.45678912)::int,5) );
SELECT round(1234.5678912, 5 - least(log(1234.5678912)::int,5) );
SELECT round(12345.678912, 5 - least(log(12345.678912)::int,5) );
SELECT round(123456.78912, 5 - least(log(123456.78912)::int,5) );
SELECT round(1234567.8912, 5 - least(log(1234567.8912)::int,5) );
SELECT round(12345678.912, 5 - least(log(12345678.912)::int,5) );
SELECT round(123456789.12, 5 - least(log(123456789.12)::int,5) );
as content for a function
with arguments (value numeric, diggits int)
Have s.o. a better idea?
Franz
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-01-24 17:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-24 14:57 format integer ml@ft-c.de
2021-01-24 16:06 ` David G. Johnston <david.g.johnston@gmail.com>
2021-01-24 16:37 ` Steve Midgley <science@misuse.org>
2021-01-24 17:35 ` ml@ft-c.de
2021-01-24 16:48 ` Torge Kummerow <tk@panaccess.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