Count Aggregate Dot Count Syntax (.count)

Question

So I ran into what I thought was a bizarre error this morning when I accidentally referred to a non-existant "count" column in my CTE. Postgres was looking for a GROUP BY clause even though I didn't think I was doing an aggregate. A little more playing around and it appears the table.count is equivalent to a count star function. Consider the following:

SELECT
    c.clinic_id,
    c.count,
    count(*) as count_star
FROM clinic_member c
GROUP BY 
 c.clinic_id
ORDER BY clinic_id;

This will generate results that look like this in my dataset:

Intrigued to understand what the actual Postgres syntax rules are, I tried searching the documentation for references to this syntax and was unable to find anything; just lots of documentation for count(*). Can anyone explain if this is valid SQL and whether there are other aggregate functions that can also be called similarily? Links to Postgres documentation would be awesome if they exist.

Note. This is on Postgres 9.5.9

how exactly are you processing this sql -- is it possible your query application is making the change for you? — Hogan, Nov 10 '17 at 19:28
Here is a SQL Fiddle: http://www.sqlfiddle.com/#!17/9eecb/5984. — Gordon Linoff, Nov 10 '17 at 19:41
In Postgres, functional notation and attribute notation are equivalent. Related: https://stackoverflow.com/questions/11165450/store-common-query-as-column/11166268#11166268 — Erwin Brandstetter, Nov 28 '17 at 13:56

score 9 · Accepted Answer · answered Nov 10 '17 at 19:41

It is valid Postgres syntax because in Postgres a function with a single argument that matches a table type can be called in two different ways:

Assuming a table name foo and a function named some_function with a single argument of type foo then the following:

select some_function(f)
from foo f;

is equivalent to

select f.some_function
from foo f;

The alias is actually not necessary:

select foo.some_function
from foo;

This is a result of the "object oriented" structure of Postgres.

count() can take any argument - including a row reference (=record) therefore

select count(f)
from foo f;

is equivalent to

select f.count
from foo f;

This is documented in the chapter about Function Calls in the manual:

A function that takes a single argument of composite type can optionally be called using field-selection syntax, and conversely field selection can be written in functional style. That is, the notations col(table) and table.col are interchangeable. This behavior is not SQL-standard but is provided in PostgreSQL because it allows use of functions to emulate “computed fields”. For more information see Section 8.16.5.

I got thrown by thinking this was a count function syntax only. I didn't even think to look through the general function call documentation. THanks — Daniel, Nov 10 '17 at 19:56

Count Aggregate Dot Count Syntax (.count)

1 Answers1

Linked