On Mon, Dec 7, 2020 at 8:45 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Ken Tanzer <ken.tanzer@xxxxxxxxx> writes:
> On Mon, Dec 7, 2020 at 8:16 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
>> 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?
Sorry I was vague there, it's the parse analysis phase that understands
that "GROUP BY a primary key" should be treated as allowing any column of
that pkey's table to be referenced without also explicitly grouping by
that other column. If you then join to some other table, the free pass
doesn't extend to the other table.
Thanks! That makes sense to me as an explanation, and is good to know.
There's one last piece of this query I'm clearly not getting though. Where it says:
from foo as f, jsonb_to_recordset(js) as t(key2 text)
what is actually going on there? I keep reading this as a table foo (f) cross-joined to a table created by jsonb_to_recordset (t). But that doesn't seem right, because rows from t are only joining with matching rows from f, rather than all of them. Is there some unspoken implicit logic going on here, or something else entirely that is going over my head?
Thanks everybody for the help and patience!
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
