It Works on SQLite and Breaks on Postgres: The GROUP BY Trap

You develop against SQLite because it is fast and needs no setup. Production runs Postgres. Everything has been fine for months. Then you write a dashboard endpoint that groups by a computed key, it works perfectly locally, and production says:

column "orders.channel" must appear in the GROUP BY clause
or be used in an aggregate function

You look at your query. The column is in the GROUP BY. You add it again. Same error. You start wondering whether Postgres is broken.

This is not an ORM quirk on its own. The underlying difference is real and worth knowing.

Postgres is right and SQLite is convenient, which is the worst possible combination for catching this early: the permissive engine is the one you develop against. Every incorrect aggregate you write passes locally.

Here is the shape that catches people, using SQLAlchemy. You want to group by a column that falls back to another when empty:

# WRONG — the same expression built twice
session.query(
    func.coalesce(func.nullif(Order.channel, ""), "unknown").label("channel"),
    func.count(Order.id),
).group_by(
    func.coalesce(func.nullif(Order.channel, ""), "unknown")   # a second object
)

Read that and it looks fine. The two expressions are textually identical. But each call to func.nullif(Order.channel, "") creates a separate expression object with its own bind parameter for the empty string. The generated SQL is closer to:

SELECT coalesce(nullif(orders.channel, :param_1), :param_2) AS channel, count(...)
FROM orders
GROUP BY coalesce(nullif(orders.channel, :param_3), :param_4)

To Postgres, :param_1 and :param_3 are different expressions, so the SELECT expression is not covered by the GROUP BY — hence the error, which names the underlying column rather than the expression, which is why it reads as nonsense.

SQLite does not care and returns the result you expected.

# RIGHT — one expression object, referenced twice
channel = func.coalesce(func.nullif(Order.channel, ""), "unknown").label("channel")

session.query(channel, func.count(Order.id)).group_by(channel)

That is the entire fix. One object, two references, one set of bind parameters, and Postgres is satisfied.

The habit worth forming: any expression that appears in both SELECT and GROUP BY gets assigned to a variable first. It reads better anyway, and it makes this class of bug impossible.

The reason this bug is common is not that the fix is hard. It is that the feedback loop is inverted.

• Local is permissive, production is strict. Normally you catch problems locally and production is the same or easier. Here it is the reverse, so your tests actively give you false confidence.
• Aggregates are where it bites. Simple CRUD behaves identically on both engines. It is dashboards, reports and analytics endpoints — usually written later, often under time pressure — that hit it.
• The error names the wrong thing. It points at a column, not at the expression mismatch, so the obvious fix (add the column to GROUP BY) does not work and sends you down a blind alley.

1. Run aggregate queries against Postgres before merging

   You do not need a full parity environment. A throwaway Postgres in Docker, used only for the analytics tests, catches essentially all of this.

2. Treat "verified locally" as not applying to aggregates

   Make it an explicit rule. Any query with GROUP BY, a window function or a HAVING clause needs a Postgres run, and that expectation belongs in your contributing notes, not in someone's head.

3. Assign shared expressions to variables

   A lint-able convention beats remembering a database subtlety at 6pm on a Friday.

The broader lesson generalises past this one error: a development database that is more permissive than production is not a convenience, it is a source of false negatives. Either match production, or know precisely which classes of query your local engine cannot validate.

Why does my query work on SQLite but fail on Postgres with a GROUP BY error?

SQLite permits selecting columns that are neither aggregated nor grouped, and matches GROUP BY expressions loosely. Postgres requires every selected non-aggregate expression to appear in GROUP BY, and matches expressions structurally including their bind parameters.

Why does adding the column to GROUP BY not fix it?

Because the problem is usually not a missing column but a duplicated expression. If you construct the same computed expression twice in an ORM, each copy carries its own bind parameters, so Postgres treats them as different expressions even though the SQL text looks identical.

What is the fix in SQLAlchemy?

Build the expression once, assign it to a variable, and pass that same object to both the select list and group_by(). One object means one set of bind parameters.

Should I develop against Postgres instead of SQLite?

For anything with aggregate queries, yes — or at minimum run those tests against a throwaway Postgres instance in CI. A more permissive local engine produces false negatives, which is worse than a slower one.

Which queries are most at risk?

Anything with GROUP BY, HAVING or window functions — typically dashboards, reports and analytics endpoints. Plain CRUD behaves the same on both engines.