Re: Strange error message when reference non-existent column foo."count"

Patrick Krecker wrote
> I encountered this today and it was quite surprising:
>
> select version();
> version
>
> ------------------------------------------------------------------------------------------------------
> PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
> 4.8.2-19ubuntu1) 4.8.2, 64-bit
>
> create table foo as (select generate_series(1,3));
>
> As expected, the following fails:
>
> select count from foo;
> ERROR: column "count" does not exist
> LINE 1: select count from foo;
> ^
> But if I change the syntax to something I thought was equivalent:
>
> select foo."count" from foo;
> count
> -------
> 3
> (1 row)
>
> It works! This was quite surprising to me. Is this expected behavior, that
> you can call an aggregate function without any parentheses (I can't find
> any other syntax that works for count() sans parentheses, and this
> behavior
> doesn't occur for any other aggregate)?

That fact that this is an aggregate function is beside the point - the
syntax works for any function.

The following two expressions are equivalent:

count(foo) = foo.count

I do not immediately recall where this is documented but it is. It should
probably be documented or cross-referenced at:

http://www.postgresql.org/docs/9.3/static/sql-syntax-calling-funcs.html#SQL-SYNTAX-CALLING-FUNCS-NAMED

but alas that is not so.

The basic idea is to hide the function invocation and allow for
syntactically similar derived columns to be described.

(goes looking)

4.2.6 - the note therein:
http://www.postgresql.org/docs/9.3/static/sql-expressions.html#FIELD-SELECTION
pointing to 35.4.3
http://www.postgresql.org/docs/9.3/static/xfunc-sql.html#XFUNC-SQL-COMPOSITE-FUNCTIONS

This relies on the rule that every table automatically has an implicit type
created and so a "composite function" can act on that type. The "foo."
reference in your example is technically referring to the type "foo" and not
the table "foo".

David J.

--
View this message in context: http://postgresql.nabble.com/Strange-error-message-when-reference-non-existent-column-foo-count-tp5831200p5831204.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.