2

I don't understand why this query:

select count(base.*) from mytable base;

does return multiple rows.

select count(1) from mytable base;

returns the proper count.

There is a column with the name count.

Can anyone please explain this behaviour?

Here is the information from the schema:

table_catalog,table_schema,table_name,column_name,ordinal_position,column_default,is_nullable,data_type,character_maximum_length,character_octet_length,numeric_precision,numeric_precision_radix,numeric_scale,datetime_precision,interval_type,interval_precision,character_set_catalog,character_set_schema,character_set_name,collation_catalog,collation_schema,collation_name,domain_catalog,domain_schema,domain_name,udt_catalog,udt_schema,udt_name,scope_catalog,scope_schema,scope_name,maximum_cardinality,dtd_identifier,is_self_referencing,is_identity,identity_generation,identity_start,identity_increment,identity_maximum,identity_minimum,identity_cycle,is_generated,generation_expression,is_updatable
mydatabase,vcs,mytable,controlepunt,1,,YES,text,,1073741824,,,,,,,,,,,,,,,,mydatabase,pg_catalog,text,,,,,1,NO,NO,,,,,,,NEVER,,YES
mydatabase,vcs,mytable,norm,2,,YES,text,,1073741824,,,,,,,,,,,,,,,,mydatabase,pg_catalog,text,,,,,2,NO,NO,,,,,,,NEVER,,YES
mydatabase,vcs,mytable,fout,3,,YES,text,,1073741824,,,,,,,,,,,,,,,,mydatabase,pg_catalog,text,,,,,3,NO,NO,,,,,,,NEVER,,YES
mydatabase,vcs,mytable,count,4,,YES,bigint,,,64,2,0,,,,,,,,,,,,,mydatabase,pg_catalog,int8,,,,,4,NO,NO,,,,,,,NEVER,,YES
mydatabase,vcs,mytable,id,5,,YES,bigint,,,64,2,0,,,,,,,,,,,,,mydatabase,pg_catalog,int8,,,,,5,NO,NO,,,,,,,NEVER,,YES
Rob Audenaerde
  • 19,195
  • 10
  • 76
  • 121

2 Answers2

2

This style is apparently called functional notation.

It makes table.col and col(table) equivalent.

And table and table.* return the same set of columns.

This question has more information on it: Using functional notation in PostgreSQL queries instead of dot notation

In the postgresql docs: https://www.postgresql.org/docs/9.1/static/xfunc-sql.html

Another option is to use functional notation for extracting an attribute. The simple way to explain this is that we can use the notations attribute(table) and table.attribute interchangeably.

Rob Audenaerde
  • 19,195
  • 10
  • 76
  • 121
1

It's not an answer - using it to extend samples for the OP. it seems not related to aggregation functions:

t=# create table s91("count" int);
CREATE TABLE
Time: 38.981 ms
t=# insert into s91 values (1),(2),(3);
INSERT 0 3
Time: 13.929 ms
t=# select count(base.*) from s91 base;
 count 
-------
     1
     2
     3
(3 rows)

t=# alter table s91 rename COLUMN a to "manah_manah";
ALTER TABLE
Time: 1.025 ms
t=# select manah_manah(s91.*) from s91;
 manah_manah 
-------------
           1
           2
           3
(3 rows)

update: seems column(alias_name) is a valid syntax:

s=# with c(a,b) as (values(1,2),(2,3))
select a(c),(c).a from c;
 a | a 
---+---
 1 | 1
 2 | 2
(2 rows)
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • Thanks for the extra effort! It seems the 'expression' is parsed using a preference towards column name. It somehow ignores the parentheses-part, but I haven't found it yet in Postgresql's `gram.y` – Rob Audenaerde Aug 18 '17 at 09:38
  • Updated sample - seems like a feature now, not the bug :) – Vao Tsun Aug 18 '17 at 09:49
  • Interesting :) I could of course switch to the `count(1)` for my application.. I hope this behavior could end up in the documentation somehow – Rob Audenaerde Aug 18 '17 at 09:51
  • syntactically this expression is closest to `function(record)` or even to casting `typename ( expression )` - anyway can't find anything alike in docs – Vao Tsun Aug 18 '17 at 10:23
  • 1
    IIRC column_names_as_functions is a relic from QUEL, which was closer to relational algebra. (there was some fuzz about it on the pg-hackers list too, a few months ago) – joop Aug 18 '17 at 11:13
  • https://www.postgresql.org/search/?m=1&q=QUEL+column&l=1&d=-1&s=r cant find - please share a link - I'd like to read on it – Vao Tsun Aug 18 '17 at 12:04