CODEWITHSUNDEEP
sqlpostgresqlcountpostgresql-9.2
Rob Audenaerde
Rob Audenaerde

Reputation: 20069

Count column with name 'count' returns multiple rows. Why?

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

Upvotes: 2

Views: 204

Answers (2)

Rob Audenaerde
Rob Audenaerde

Reputation: 20069

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.

Upvotes: 2

Vao Tsun
Vao Tsun

Reputation: 51599

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)

Upvotes: 1

Related Questions