Обсуждение: Strange error message when reference non-existent column foo."count"

Поиск
Список
Период
Сортировка

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

От
Patrick Krecker
Дата:
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)?

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

От
Patrick Krecker
Дата:
Sorry, I changed the email as I was writing it but I forgot to change the subject line. An appropriate subject would be 'Strange behavior when referencing non-existent column foo."count".'

On Wed, Dec 17, 2014 at 2:50 PM, Patrick Krecker <patrick@judicata.com> 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)?

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

От
Tom Lane
Дата:
Patrick Krecker <patrick@judicata.com> writes:
> 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,

Yes.  foo.bar is equivalent to bar(foo) in Postgres.  It is documented;
see for instance the Note here:
http://www.postgresql.org/docs/9.3/static/sql-expressions.html#SQL-EXPRESSIONS-FUNCTION-CALLS

> 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)?

It occurs for any function at all, aggregate or otherwise, if the function
can accept the table's composite type as argument.  The alternatives you
tried probably were not things that could take a composite-type argument.
count() is pretty lax about what it will take, since it only cares about
is-null-or-not.

            regards, tom lane


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

От
David G Johnston
Дата:
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.


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

От
Patrick Krecker
Дата:


On Wed, Dec 17, 2014 at 3:11 PM, David G Johnston <david.g.johnston@gmail.com> wrote:
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.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Well, that clears it up. Thanks!