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.)

Answers


The whole quote is:

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


Need Your Help

How to force Castle.DynamicProxy to ignore changing versions of dependencies

c# aop interceptor castle-dynamicproxy

I use Castle.DynamicProxy for AOP. My IoC container is Ninject 3.2 and after it returns resolved dependencies, I put the most of them to a Castle-based wrapper. (I can't use Ninject.Extensions.

Python: how to convert pickled txt files into gpickles for networkx?

python pickle networkx file-conversion

After working with a number of different occurrences of the same graph G, I dumped them as txt files with pickle using this line: