Setting the number of digits after the decimal point with a psql's pset variable
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t254448psql -h localhost -U postgresBuilt from patchset v1 (message #1), November 01, 2026 at 09:00 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t254448_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t254448_1 && git checkout t254448_1Patchset v1 (message #1) is on t254448_1
Hello,
Each time I execute a query to get some ratio or percentage, I end up
with a lot of usually useless digits after the decimal point. For example:
postgres=> select datname,
100.*blks_hit/(blks_read+blks_hit) as hit_ratio
from pg_stat_database
order by 1
limit 5;
datname | hit_ratio
---------+---------------------
b1 | 70.0758884178573298
b2 | 97.3346067025525265
b3 | 97.8544316239962496
b4 | 31.8874531850956484
b5 | 43.0444853056359762
(5 rows)
I would rather get only two digits (or one, or even less) after the
decimal point. I know I can do that easily by wrapping the second column
with a call to round(). Easily because this query is simple. But
sometimes, the query is much bigger, the number of columns which need a
call to round() is high enough to make me stop doing it or get pissed
because it gets quickly error prone. I need something quicker and
easier. Something like a pset variable which would allow me to tell psql
how many digits I want after the decimal point. The patch attached does
that. It adds a pset variable called n_decimals, which defaults to all,
meaning no changes in psql's behaviour. A user can set it to any
positive number to get that number of digits after the decimal point. An
example:
postgres=> \pset n_decimals 2
Number of decimals is "2".
postgres=> \g
datname | hit_ratio
---------+-----------
b1 | 70.10
b2 | 97.33
b3 | 97.85
b4 | 31.89
b5 | 43.05
(5 rows)
postgres=> \pset n_decimals 0
Number of decimals is "0".
postgres=> \g
datname | hit_ratio
---------+-----------
b1 | 70
b2 | 97
b3 | 97
b4 | 31
b5 | 43
(5 rows)
postgres=> \pset n_decimals
Number of decimals is "0".
postgres=> \pset n_decimals all
Number of decimals is "all".
postgres=> \g
datname | hit_ratio
---------+---------------------
b1 | 70.1052134669652421
b2 | 97.3396221743544034
b3 | 97.8583698372493909
b4 | 31.8998905599223468
b5 | 43.0615702842088872
(5 rows)
Any comments welcome :)
Thank you.
Regards.
--
Guillaume Lelarge
Consultant
https://dalibo.com
Hi,
On Thu, Oct 29, 2026 at 09:45:54PM +0100, Guillaume Lelarge wrote:
Hello,
Any comments welcome :)
I did not look at the details but I was a bit skeptical at first, as I wondered
whether this would open the door to similar formatting options for other data
types. Then I did some research and found that 645cb44c5 kind of already opened
the door, so I'm not sure what to think about it.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
On Fri, Oct 30, 2026 at 08:41:35AM +0000, Bertrand Drouvot wrote:
On Thu, Oct 29, 2026 at 09:45:54PM +0100, Guillaume Lelarge wrote:
Any comments welcome :)
I did not look at the details but I was a bit skeptical at first, as I wondered
whether this would open the door to similar formatting options for other data
types. Then I did some research and found that 645cb44c5 kind of already opened
the door, so I'm not sure what to think about it.
I'm +1 for the idea. I've actually wanted something like $subject a handful
of times in the past, usually when trying to format a table to be readable
on the lists. It'd be nice to not have to amend the query with calls to
round().
--
nathan
On 30/10/2026 20:47, Nathan Bossart wrote:
On Fri, Oct 30, 2026 at 08:41:35AM +0000, Bertrand Drouvot wrote:
On Thu, Oct 29, 2026 at 09:45:54PM +0100, Guillaume Lelarge wrote:
Any comments welcome :)
I did not look at the details but I was a bit skeptical at first, as I wondered
whether this would open the door to similar formatting options for other data
types. Then I did some research and found that 645cb44c5 kind of already opened
the door, so I'm not sure what to think about it.I'm +1 for the idea. I've actually wanted something like $subject a handful
of times in the past, usually when trying to format a table to be readable
on the lists. It'd be nice to not have to amend the query with calls to
round().
That's exactly my need. I'm at a customer's office trying to get some
metrics. Having lots of number like 23.12346547987 is not helpful, and
makes it harder to understand the values, especially when the table ends
up being badly formatted (yeah, I know \x).
Anyway, \pset seems the right way to get that kind of feature.
--
Guillaume Lelarge
Consultant
https://dalibo.com
Hi,
On Fri, Oct 30, 2026 at 09:19:24PM +0100, Guillaume Lelarge wrote:
On 30/10/2026 20:47, Nathan Bossart wrote:
On Fri, Oct 30, 2026 at 08:41:35AM +0000, Bertrand Drouvot wrote:
On Thu, Oct 29, 2026 at 09:45:54PM +0100, Guillaume Lelarge wrote:
Any comments welcome :)
I did not look at the details but I was a bit skeptical at first, as I wondered
whether this would open the door to similar formatting options for other data
types. Then I did some research and found that 645cb44c5 kind of already opened
the door, so I'm not sure what to think about it.I'm +1 for the idea. I've actually wanted something like $subject a handful
of times in the past, usually when trying to format a table to be readable
on the lists. It'd be nice to not have to amend the query with calls to
round().
Yeah, I can see the use case described here and 645cb44c5 already set a precedent
for this.
That's exactly my need. I'm at a customer's office trying to get some
metrics. Having lots of number like 23.12346547987 is not helpful, and makes
it harder to understand the values, especially when the table ends up being
badly formatted (yeah, I know \x).Anyway, \pset seems the right way to get that kind of feature.
As far the patch, a few comments:
=== 1
+ /* copy the "n_decimals" rest */
+ for (int j = 1; j <= n_decimals ; j++)
+ {
+ new_str[new_str_pos++] = my_str[i++];
+ }This can read past my_str and write past new_str. For example:
\pset n_decimals 2
SELECT 1::numeric;
produces:
=================================================================
==3574709==ERROR: AddressSanitizer: heap-buffer-overflow....
WRITE of size 1 at 0x502000008732 thread T0
#0 0x48621f in format_ndecimals /home/postgres/postgresql/postgres/src/fe_utils/print.c:444
#1 0x4977cb in printQuery /home/postgres/postgresql/postgres/src/fe_utils/print.c:3907
on an ASAN enabled build.
Here, only two bytes are allocated for "1". I think the code needs to check the
available fractional digits and compute the required output size before copying.
=== 2
postgres=# \pset n_decimals 2
Number of decimals is "2".
postgres=# SELECT 1.239::numeric, 9.999::numeric;
numeric | numeric
---------+---------
1.23 | 9.99
This looks like truncation rather than rounding. IIUC the intended behavior is
equivalent to round().
=== 3
+ if (n_decimals > 0)
+ {
+ /* add the decimal point */
+ new_str[new_str_pos++] = my_str[i++];The code assumes that the character after the integer part is a decimal point,
but what about things like?
postgres=# \pset n_decimals 0
Number of decimals is "0".
postgres=# SELECT 1e100::float8;
float8
--------
1
=== 4
+ if ((PQftype(result, c) == FLOAT4OID ||
+ PQftype(result, c) == FLOAT8OID ||
+ PQftype(result, c) == NUMERICOID) &&
+ opt->n_decimals > -1)
+ {
+ cell = format_ndecimals(cell, opt->n_decimals);
+ }format_ndecimals() returns newly allocated memory. Before this patch, replacing
cell in the numericlocale branch was safe because it still pointed into the PGresult.
With this patch, the allocation leaks in both cases: when numericlocale is disabled,
mustfree remains false. When it is enabled, cell is overwritten by a second
allocation.
=== 5
+ intval = atoi(value);
+ if (intval < -1)
+ {
+ intval = -1;
+ }atoi() silently accepts malformed values. For example, we could:
postgres=# \pset n_decimals foo
Number of decimals is "0".
postgres=# \pset n_decimals 2toto
Number of decimals is "2".
Worth using ParseVariableNum() here?
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com