This article might help understanding the reason - https://dba.stackexchange.com/questions/158015/why-can-i-select-all-fields-when-grouping-by-primary-key-but-not-when-grouping-b
From the postgres docs:
"When
GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column."If you come from an Oracle background (as I do), this behaviour may surprise you, since Oracle definitely doesn't allow this.
I much prefer Postgres. 😁
Steve
On Tue, Dec 8, 2020 at 3:32 PM Ken Tanzer <ken.tanzer@xxxxxxxxx> wrote:
On Mon, Dec 7, 2020 at 8:16 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:Ken Tanzer <ken.tanzer@xxxxxxxxx> writes:
>> => select f.id, f.f1,f.f2,array_agg(t.key2) from foo as f,
>> jsonb_to_recordset(js) as t(key2 text) group by f.id;
> After a little more thought and experimenting, I'm not so sure about this
> part. In particular, I'm not clear why Postgres isn't complaining about
> the f1 and f2 fields. (It's not giving the "must appear in the GROUP BY
> clause or be used in an aggregate function" error that I would expect, and
> that I am getting when I try to apply this to my real query.)
> Can anyone explain to me why those fields don't need to be grouped? Thanks.
If foo.id isn't a primary key, then I'm confused too. Can we see the
full declaration of the table?
So I created some confusion because the original version of the table in my example did _not_ declare a primary key. A later example, and the one I used, did have the primary key:CREATE TEMP TABLE foo (
id INTEGER PRIMARY KEY,
f1 TEXT,
f2 TEXT,
js JSONB
);If foo.id is a primary key, it knows that the "group by" doesn't really
merge any rows of foo, so it lets you get away with that. I think this
is actually required by spec, but am too lazy to go check right now.If I do that without the Primary Key, it does indeed complain about f1 & f2 not being grouped. But what is the "It" in "it lets you get away with that" referring to? Or more specifically, is this some specialized case because of something related to use of the jsonb_recordset function? I've gotten so used to having to group on every non-aggregate field that I didn't realize there could be any exception to that.Thanks!--AGENCY SoftwareA Free Software data systemBy and for non-profits(253) 245-3801learn more about AGENCY orfollow the discussion.
