Why is Postgres Allowing Me Have Columns not in the Group By?
Why does this query:
SELECT inv.batch_number_id AS batch_number_id, inv.expiry_date AS expiry_date, inv.facility_id AS facility_id, inv.id AS id, fac.code AS facility_code, inv_st.description AS status, bn.batch_nbr AS batch_number, bn.expiry_date AS batch_expiry_date, sum(CASE WHEN al.status_id < 90 THEN al.alloc_qty ELSE 0 END) AS alloc_qty FROM inventory inv INNER JOIN facility fac ON inv.facility_id = fac.id INNER JOIN inventory_status inv_st ON inv.status_id = inv_st.id LEFT OUTER JOIN batch_number bn ON inv.batch_number_id = bn.id LEFT OUTER JOIN allocation al ON al.from_inventory_id = inv.id GROUP BY inv.id, facility_code, status, batch_number, batch_expiry_date
work in postgres 9.1.6?
From the docs: "When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column."
If I add fac.id to the select expression, I get the expected "ERROR: column "fac.id" must appear in the GROUP BY clause or be used in an aggregate function". Why is postgres complaining about that column but ignoring facility_id? (There're a whole bunch of other columns in the original query that also violate my expectations in the same way. The example was shortened for clarity.)
When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or if 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.
UPDATE: this feature is added in 9.1