Subject: Re: Adjust error message for CREATE STATISTICS to account for expressions

From: John Naylor <[email-masked]>
Date: Wed, 25 Mar 2026 08:24:40 +0700

On Tue, Mar 24, 2026 at 6:34 PM Yugo Nagata <[email-masked]> wrote:
>
> Hi,
>
> I've attached a patch to adjust the error message for CREATE STATISTICS
> to account for expressions.
>
> Previously, when attempting to create extended statistics on a single column,
> the following error is raised.
>
>  ERROR:  extended statistics require at least 2 columns
>
> However, this message assumed only columns. In reality, an expression on a
> single column is also allowed, so two columns are not necessarily required.
> Thgis patch updates the message to:
>
>  ERROR:  extended statistics require at least 2 columns or an expression

I'm ambivalent about this. Instead of trying to document all the
possible valid possibilities (which may get out of date again), maybe
we can say that it's not allowed on a single column?

--
John Naylor
Amazon Web Services

---

From: John Naylor <[email-masked]>
Date: Thu, 26 Mar 2026 16:22:07 +0700

On Wed, Mar 25, 2026 at 11:15 AM Yugo Nagata <[email-masked]> wrote:
>
> On Wed, 25 Mar 2026 08:24:40 +0700
> John Naylor <[email-masked]> wrote:

> Thank you for the suggestion. I agree that this is a simpler approach,
> so I've attached a revised patch.

- errmsg("extended statistics require at least 2 columns")));
+ errmsg("extended statistics are not supported on a single column")));

I'd probably write this as "extended statistics on a single column are
not supported" (no need to send a new patch).

Any objections?

Also, I'm leaning towards not backpatching -- I figure a user that got
here by fat-fingering an expression is not likely to be too confused.

I looked in the feature thread and saw that this was actually raised
during development of the feature:

https://www.postgresql.org/message-id/CAEZATCU9uPo7JYdx4k0-ufXXZH8t7itodibUwCva%2Bs%2BAvAKcnw%40mail.gmail.com

-- 
John Naylor
Amazon Web Services

---

From: Michael Paquier <[email-masked]>
Date: Fri, 27 Mar 2026 08:23:45 +0900

> not supported" (no need to send a new patch).

None.  I have found this error message puzzling while doing some of my
recent work for extended statistics, because extended statistics can
be created if there is a single element made of an expression in the
list given in input.

Now our documentation also tells that the former message is not the
preferred project style (full sentences usually avoided in primary
messages):
https://www.postgresql.org/docs/devel/error-style-guide.html#ERROR-STYLE-GUIDE-GRAMMAR-PUNCTUATION

Perhaps something like "could not create extended statistics" with a
hint describing the cause would be better..  Full sentences usually
apply to errdetails or errhints.  If we're on it, we could just as
well improve the whole thing, I guess?
--
Michael

---

From: John Naylor <[email-masked]>
Date: Fri, 27 Mar 2026 07:45:37 +0700

> https://www.postgresql.org/docs/devel/error-style-guide.html#ERROR-STYLE-GUIDE-GRAMMAR-PUNCTUATION

My reading of that is that hints/details need to be complete
sentences, and primary messages don't need to be. If the obvious way
to write primary message is with a complete sentence, I would say
that's fine, but I don't have a lot of context here.

> well improve the whole thing, I guess?

I don't feel strongly either way, so in that case I would default to
not changing to errdetail. Looking elsewhere in this file, however, I
do see some messages with "cannot do X because ....", and I'd be more
motivated to turn those "because" phrases into errdetails.

-- 
John Naylor
Amazon Web Services

---

From: Dean Rasheed <[email-masked]>
Date: Fri, 27 Mar 2026 09:50:07 +0000

On Fri, 27 Mar 2026 at 04:46, Yugo Nagata <[email-masked]> wrote:
>
> On Fri, 27 Mar 2026 07:45:37 +0700
> I don't have a strong preference here, so I'm fine with either approach.
> I also agree that moving "because ..." into errdetails in some longer
> messages makes sense.

I don't think it should be a hint, because that's more for suggestions
on how to fix the problem.

Given that the user just tried to create extended statistics on a
single column, they're probably not interested in expression
statistics or multivariate statistics, so a single short "not
supported" error seems sufficient.

If we were to give more detail, it should probably be to point out
that regular statistics will already be built for single columns,
which is why single-column extended statistics would be redundant. So
perhaps something like this:

Error: cannot create extended statistics on a single column
Detail: Univariate statistics are already built for each individual
table column.

Regards,
Dean

---

From: Chao Li <[email-masked]>
Date: Mon, 30 Mar 2026 11:19:36 +0800

> On Mar 27, 2026, at 08:45, John Naylor <[email-masked]> wrote:
> 
> sentences, and primary messages don't need to be. 

+1

> From: Dean Rasheed <[email-masked]>
> 
> 

I like this version.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

---

From: John Naylor <[email-masked]>
Date: Mon, 30 Mar 2026 10:21:54 +0700

On Sat, Mar 28, 2026 at 1:04 PM Yugo Nagata <[email-masked]> wrote:
>
> On Fri, 27 Mar 2026 09:50:07 +0000
> Dean Rasheed <[email-masked]> wrote:

> That makes sense to me.
> Using an errdetail to explain the reason sounds reasonable.

Works for me.

> > > > Looking elsewhere in this file, however, I

Did you also want to try tackling this while we're here?

--
John Naylor
Amazon Web Services

---

From: Dean Rasheed <[email-masked]>
Date: Wed, 1 Apr 2026 17:19:16 +0100

On Tue, 31 Mar 2026 at 15:47, Yugo Nagata <[email-masked]> wrote:
>
> Yes, that sounds good to me.
>
> In particular, the following suggestion from Dean Rasheed seems like a
> good approach:
>
> > table column

I've pushed that change as part of the patch to support extended
statistics on virtual generated columns [1], since I felt that patch
already needed to change that error message.

I didn't look more widely at other error messages in that file might
benefit from having separate errdetail text.

Regards,
Dean

[1] https://www.postgresql.org/message-id/flat/[email-masked]

---

From: Yugo Nagata <[email-masked]>
Date: Fri, 3 Apr 2026 10:44:53 +0900

On Wed, 1 Apr 2026 17:19:16 +0100

> already needed to change that error message.

Thank you for handling this.
 
> benefit from having separate errdetail text.

I’ve attached a patch that also updates other error messages to move
their reason parts into errdetail.

Regards,
Yugo Nagata

-- 
Yugo Nagata <[email-masked]>
diff --git a/src/backend/commands/statscmds.c b/src/backend/commands/statscmds.c
index eea45106a3f..b354723be44 100644
--- a/src/backend/commands/statscmds.c
+++ b/src/backend/commands/statscmds.c
@@ -280,8 +280,10 @@ CreateStatistics(CreateStatsStmt *stmt, bool check_rights)
 				if (type->lt_opr == InvalidOid)
 					ereport(ERROR,
 							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-							 errmsg("column \"%s\" cannot be used in multivariate statistics because its type %s has no default btree operator class",
-									attname, format_type_be(attForm->atttypid))));
+							 errmsg("cannot create multivariate statistics on column \"%s\"",
+									attname),
+							 errdetail("The type %s has no default btree operator class.",
+									   format_type_be(attForm->atttypid))));
 			}
 
 			/* Treat virtual generated columns as expressions */
@@ -325,8 +327,10 @@ CreateStatistics(CreateStatsStmt *stmt, bool check_rights)
 				if (type->lt_opr == InvalidOid)
 					ereport(ERROR,
 							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-							 errmsg("column \"%s\" cannot be used in multivariate statistics because its type %s has no default btree operator class",
-									get_attname(relid, var->varattno, false), format_type_be(var->vartype))));
+							 errmsg("cannot create multivariate statistics on column \"%s\"",
+									get_attname(relid, var->varattno, false)),
+							 errdetail("The type %s has no default btree operator class.",
+									   format_type_be(var->vartype))));
 			}
 
 			/* Treat virtual generated columns as expressions */
@@ -375,8 +379,9 @@ CreateStatistics(CreateStatsStmt *stmt, bool check_rights)
 				if (type->lt_opr == InvalidOid)
 					ereport(ERROR,
 							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-							 errmsg("expression cannot be used in multivariate statistics because its type %s has no default btree operator class",
-									format_type_be(atttype))));
+							 errmsg("cannot create multivariate statistics on this expression"),
+							 errdetail("The type %s has no default btree operator class.",
+									   format_type_be(atttype))));
 			}
 
 			stxexprs = lappend(stxexprs, expr);
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index c6ba2479413..37070c1a896 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -99,7 +99,8 @@ CREATE STATISTICS tst (ndistinct) ON (y + z) FROM ext_stats_test;
 ERROR:  cannot specify statistics kinds when building univariate statistics
 -- multivariate statistics without a less-than operator not supported
 CREATE STATISTICS tst (ndistinct) ON x, w from ext_stats_test;
-ERROR:  column "w" cannot be used in multivariate statistics because its type xid has no default btree operator class
+ERROR:  cannot create multivariate statistics on column "w"
+DETAIL:  The type xid has no default btree operator class.
 DROP TABLE ext_stats_test;
 -- Ensure stats are dropped sanely, and test IF NOT EXISTS while at it
 CREATE TABLE ab1 (a INTEGER, b INTEGER, c INTEGER);

---

From: John Naylor <[email-masked]>
Date: Tue, 7 Apr 2026 11:40:16 +0700

> their reason parts into errdetail.

Pushed, thanks!

-- 
John Naylor
Amazon Web Services