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
From b01f08452cc9bfceca552ff935dd2a14e9e58f79 Mon Sep 17 00:00:00 2001
From: Guillaume Lelarge <[email protected]>
Date: Wed, 28 Oct 2026 21:57:54 +0100
Subject: [PATCH] Add new n_decimals pset variable
This variable rounds each query's floating point or numeric value to
this number of decimals.
The variable is by default at "all", meaning no change. A negative value
is the same as setting "all". A positive value corresponds to the number
of digits after the decimal point.
---
doc/src/sgml/ref/psql-ref.sgml | 10 +++++
src/bin/psql/command.c | 54 ++++++++++++++++++++++-
src/bin/psql/help.c | 2 +
src/bin/psql/startup.c | 2 +
src/bin/psql/tab-complete.in.c | 2 +-
src/fe_utils/print.c | 71 ++++++++++++++++++++++++++++++
src/include/fe_utils/print.h | 1 +
src/test/regress/expected/psql.out | 1 +
8 files changed, 140 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 4058caf061b..405b95c4334 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3314,6 +3314,16 @@ SELECT $1 \parse stmt1
</listitem>
</varlistentry>
+ <varlistentry id="app-psql-meta-command-pset-n_decimals">
+ <term><literal>n_decimals</literal></term>
+ <listitem>
+ <para>
+ Sets the number of digits after the decimal point. Valid values are
+ <literal>all</literal>, and an integer value.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="app-psql-meta-command-pset-null">
<term><literal>null</literal></term>
<listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index e0c4d7a3562..04990534267 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -2714,8 +2714,8 @@ exec_command_pset(PsqlScanState scan_state, bool active_branch)
static const char *const my_list[] = {
"border", "columns", "csv_fieldsep",
"display_false", "display_true", "expanded", "fieldsep",
- "fieldsep_zero", "footer", "format", "linestyle", "null",
- "numericlocale", "pager", "pager_min_lines",
+ "fieldsep_zero", "footer", "format", "linestyle", "n_decimals",
+ "null", "numericlocale", "pager", "pager_min_lines",
"recordsep", "recordsep_zero",
"tableattr", "title", "tuples_only",
"unicode_border_linestyle",
@@ -5345,6 +5345,26 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
popt->topt.fieldSep.separator_zero = true;
}
+ /* number of decimals */
+ else if (strcmp(param, "n_decimals") == 0)
+ {
+ if (value)
+ {
+ int intval = -1;
+
+ if (pg_strcasecmp(value, "all") != 0)
+ {
+ intval = atoi(value);
+ if (intval < -1)
+ {
+ intval = -1;
+ }
+ }
+
+ popt->n_decimals = intval;
+ }
+ }
+
/* record separator for unaligned text */
else if (strcmp(param, "recordsep") == 0)
{
@@ -5552,6 +5572,19 @@ printPsetInfo(const char *param, printQueryOpt *popt)
get_line_style(&popt->topt)->name);
}
+ /* show number of decimals */
+ else if (strcmp(param, "n_decimals") == 0)
+ {
+ if (popt->n_decimals == -1)
+ {
+ printf(_("Number of decimals is \"all\".\n"));
+ }
+ else
+ {
+ printf(_("Number of decimals is \"%d\".\n"), popt->n_decimals);
+ }
+ }
+
/* show null display */
else if (strcmp(param, "null") == 0)
{
@@ -5691,6 +5724,8 @@ savePsetInfo(const printQueryOpt *popt)
save->falsePrint = pg_strdup(popt->falsePrint);
if (popt->title)
save->title = pg_strdup(popt->title);
+ if (popt->n_decimals)
+ save->n_decimals = popt->n_decimals;
/*
* footers and translate_columns are never set in psql's print settings,
@@ -5810,6 +5845,21 @@ pset_value_string(const char *param, printQueryOpt *popt)
return pstrdup(_align2string(popt->topt.format));
else if (strcmp(param, "linestyle") == 0)
return pstrdup(get_line_style(&popt->topt)->name);
+ else if (strcmp(param, "n_decimals") == 0)
+ {
+ /* must be PRINT_XHEADER_EXACT_WIDTH */
+ char wbuff[32];
+
+ if (popt->n_decimals == -1)
+ {
+ snprintf(wbuff, sizeof(wbuff), "all");
+ }
+ else
+ {
+ snprintf(wbuff, sizeof(wbuff), "%d", popt->n_decimals);
+ }
+ return pstrdup(wbuff);
+ }
else if (strcmp(param, "null") == 0)
return pset_quoted_string(popt->nullPrint
? popt->nullPrint
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 0def23418d5..b71b69de4e0 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -496,6 +496,8 @@ helpVariables(unsigned short int pager)
" set output format [unaligned, aligned, wrapped, html, asciidoc, ...]\n");
HELP0(" linestyle\n"
" set the border line drawing style [ascii, old-ascii, unicode]\n");
+ HELP0(" n_decimals\n"
+ " set number of digits after the decimal point [all, integer value]\n");
HELP0(" null\n"
" set the string to be printed in place of a null value\n");
HELP0(" numericlocale\n"
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index bb33c7c14d7..66b09eb64ed 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -163,6 +163,8 @@ main(int argc, char *argv[])
pset.cur_cmd_source = stdin;
pset.cur_cmd_interactive = false;
+ pset.popt.n_decimals = -1;
+
/* We rely on unmentioned fields of pset.popt to start out 0/false/NULL */
pset.popt.topt.format = PRINT_ALIGNED;
pset.popt.topt.border = 1;
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 96de78cd087..16842ffa91d 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -5679,7 +5679,7 @@ match_previous_words(int pattern_id,
COMPLETE_WITH_CS("border", "columns", "csv_fieldsep",
"display_false", "display_true", "expanded",
"fieldsep", "fieldsep_zero", "footer", "format",
- "linestyle", "null", "numericlocale",
+ "linestyle", "n_decimals", "null", "numericlocale",
"pager", "pager_min_lines",
"recordsep", "recordsep_zero",
"tableattr", "title", "tuples_only",
diff --git a/src/fe_utils/print.c b/src/fe_utils/print.c
index 73b12625435..55fef4b6471 100644
--- a/src/fe_utils/print.c
+++ b/src/fe_utils/print.c
@@ -392,6 +392,68 @@ format_numeric_locale(const char *my_str)
}
+/*
+ * Format a numeric value per current n_decimals setting
+ *
+ * Returns the appropriately formatted string in a new allocated block,
+ * caller must free.
+ */
+static char *
+format_ndecimals(const char *my_str, int n_decimals)
+{
+ char *new_str;
+ int new_len,
+ int_len,
+ i,
+ new_str_pos;
+
+ /*
+ * If the string doesn't look like a number, return it unchanged. This
+ * check is essential to avoid mangling already-localized "money" values.
+ */
+ if (strspn(my_str, "0123456789+-.eE") != strlen(my_str))
+ return pg_strdup(my_str);
+
+ new_len = strlen(my_str);// + additional_numeric_locale_len(my_str);
+ new_str = pg_malloc_array(char, (new_len + 1));
+ new_str_pos = 0;
+ int_len = integer_digits(my_str);
+
+ /* process sign */
+ if (my_str[0] == '-' || my_str[0] == '+')
+ {
+ new_str[new_str_pos++] = my_str[0];
+ my_str++;
+ }
+
+ /* process integer part of number plus the decimal point */
+ for (i = 0; i < int_len; i++)
+ {
+ new_str[new_str_pos++] = my_str[i];
+ }
+
+ /* process the decimal point if we have digits after the decimal point */
+ if (n_decimals > 0)
+ {
+ /* add the decimal point */
+ new_str[new_str_pos++] = my_str[i++];
+
+ /* copy the "n_decimals" rest */
+ for (int j = 1; j <= n_decimals ; j++)
+ {
+ new_str[new_str_pos++] = my_str[i++];
+ }
+ }
+
+ new_str[new_str_pos++] = '\0';
+
+ /* assert we didn't underestimate new_len (an overestimate is OK) */
+ Assert(strlen(new_str) <= new_len);
+
+ return new_str;
+}
+
+
static void
print_separator(struct separator sep, FILE *fout)
{
@@ -3836,6 +3898,15 @@ printQuery(const PGresult *result, const printQueryOpt *opt,
else
{
cell = PQgetvalue(result, r, c);
+
+ if ((PQftype(result, c) == FLOAT4OID ||
+ PQftype(result, c) == FLOAT8OID ||
+ PQftype(result, c) == NUMERICOID) &&
+ opt->n_decimals > -1)
+ {
+ cell = format_ndecimals(cell, opt->n_decimals);
+ }
+
if (cont.aligns[c] == 'r' && opt->topt.numericLocale)
{
cell = format_numeric_locale(cell);
diff --git a/src/include/fe_utils/print.h b/src/include/fe_utils/print.h
index 138ec2f2007..9a82f946cc2 100644
--- a/src/include/fe_utils/print.h
+++ b/src/include/fe_utils/print.h
@@ -191,6 +191,7 @@ typedef struct printQueryOpt
const bool *translate_columns; /* translate_columns[i-1] => do gettext on
* col i */
int n_translate_columns; /* length of translate_columns[] */
+ int n_decimals; /* number of decimals */
} printQueryOpt;
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index e8605dd041f..c54379751e8 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -453,6 +453,7 @@ fieldsep_zero off
footer on
format aligned
linestyle ascii
+n_decimals all
null ''
numericlocale off
pager 1
--
2.56.0
