A minority of my sqlc queries use CASE expressions, but I've never had an issue with them. As far as I can see, Postgres is good at optimizing them away. Is that not generally the case?
I wonder if a sufficiently smart sqlc could do the optimization itself. Basically have an SQL parser that is able to identify dynamic parts statically and encoding an efficient representation that allows constant folding at runtime.
Another weak point in sqlc is the absence of any composability, so every query has to be self-contained. But views can help you with that.
PostgreSQL is probably smarter than SQLite. :)
Anyway, at minimum you have prepared statements where a given plan may be great for one execution and terrible for the next. Maybe the database re-prepares for you, and then it's the same as a dynamic query.
Postgres is smarter about that, too! It evaluates whether the prepared statement is sensitive to parameter changes, and will either use a "generic" plan that's shared across all invocations, or a custom one that is hardwired to the values. There's a setting that controls the behaviour.
https://www.postgresql.org/docs/current/sql-prepare.html
I've not compared the actual behaviour in the codebases I work on, however.