-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jack Christensen
Sent: Friday, December 09, 2011 5:48 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Why does aggregate query allow select of non-group by or
aggregate values?
CREATE TABLE people(
id serial PRIMARY KEY,
name varchar NOT NULL
);
INSERT INTO people(name) VALUES('Adam'), ('Adam'), ('Adam'), ('Bill'),
('Sam'), ('Joe'), ('Joe');
SELECT name, count(*), random()
FROM people
GROUP BY name;
I would expect this query to cause an error because of random(). I ran into
this using an array produced by a subquery as a column in the select of an
aggregate query, but I was able to boil it down to this contrived example.
Shouldn't any expression that is not in the group by or an aggregate
function be rejected?
What am I not understanding?
Thanks.
----------------------------------------------------------------------------
-------
Functions are evaluated once for each row that it generated by the
surrounding query. This is particularly useful if the function in question
takes an aggregate as an input:
SELECT col1, array_processing_function( ARRAY_AGG( col2 ) )
FROM table
GROUP BY col1;
Without this particular behavior you would need to sub-query.
From a layman's perspective the reason why you cannot use non-aggregates
outside of GROUP BY it that it is ambiguous as to what value to output; with
an uncorrelated function call that is not the case.
David J.
