Thanks to Vik Fearing’s talk about PostgreSQL and the standard SQL (a talk delivered so far at pgconf.de and pgconf.be, I discovered the reason we use a * in count(*) is pedantry.

Let me rewind a little and tell the story…

So, most of us know there is no valid reason to use count(*) instead of count() than “some folks during the 80s thought it was ugly to use count()”. Most of us also know the * in count(*) has not the same meaning as the * in select *, which is very confusing for a lot of people.

But, what Vik pointed out in his talk is that there is an inconsistency in the SQL Standard because you have to use a * in select count(*) but you don’t have to use the * for the window function row_number()!

Indeed, should you try count() under psql, here’s your message error:

laetitia=# select count() from test;
2022-05-21 09:48:26.497 CEST [73961] ERROR:  count(*) must be used to call a parameterless aggregate function at character 8
2022-05-21 09:48:26.497 CEST [73961] STATEMENT:  select count() from test;
ERROR:  count(*) must be used to call a parameterless aggregate function
LINE 1: select count() from test;

Vik loves his * and would like to use row_number(*), which you can already do. But I wanted to remove the error message when using count(). After all, if there’s no reason to have some piece of code, this code can simply be removed!

Let’s look at Postgres code to find out where this message is displayed:

laetitia:~/tech/laetitia/postgresql|master ⇒  grep "must be used to call a parameterless aggregate function" -r . --exclude="*.po"
./src/test/regress/expected/window.out:ERROR:  count(*) must be used to call a parameterless aggregate function
./src/backend/parser/parse_func.c:                                       errmsg("%s(*) must be used to call a parameterless aggregate function",
./src/backend/parser/parse_func.c:                                       errmsg("%s(*) must be used to call a parameterless aggregate function",

So, the test is made in a file rightly named parse_func.c.

/*
 * Reject attempt to call a parameterless aggregate without (*)
 * syntax.  This is mere pedantry but some folks insisted ...
 */
if (fargs == NIL && !agg_star && !agg_within_group)
	ereport(ERROR,
			(errcode(ERRCODE_WRONG_OBJECT_TYPE),
			 errmsg("%s(*) must be used to call a parameterless aggregate function",
					NameListToString(funcname)),
			 parser_errposition(pstate, location)));

Ok, so, regarding the comment, event the PGDG was not convinced it was a good thing.

This code is written twice, let’s simply remove it and see if this new version still passes the regression tests!

We can play the regression tests against a temporary install of postgres using make check.

One test fails:

========================
 1 of 214 tests failed.
========================

Let’s see the diff:

 LINE 1: SELECT rank() OVER (PARTITION BY four, ORDER BY ten) FROM te...
                                                ^
 SELECT count() OVER () FROM tenk1;
-ERROR:  count(*) must be used to call a parameterless aggregate function
-LINE 1: SELECT count() OVER () FROM tenk1;
-               ^

As the only goal of this test is to reject any count(), let’s simply remove it.

Taaaadaaaaa!

=======================
 All 214 tests passed.
=======================

Let’s test it on my brand new instance:

laetitia=# select count()
laetitia-# from test;
 count 
-------
     6
(1 row)

What a great achievement! Now, Postgres is more consistent than the SQL Standard!

This patch has been sent to the hackers' mailing list. Feel free to support it or reject it!