If you’ve been there, you know how this goes. At first it’s kind of fun. You got into this profession because you like solving difficult problems. You start throwing down console.info(thing, "<<<<<<"); everywhere because breakpoints are for actual bugs and this is just a minor inconvenience. Surely it will just take a few passes through the logic before you’re back on track.
The first sign of trouble comes when you notice your coffee is stone cold and your laptop is at 48% battery. You’ve started on the initial orbit of your development bug death spiral. You realize you’ve been tweaking parentheses placements in your query for 45 minutes instead of looking up the postgres error code, so you decide maybe it’s time for a brain break. You walk around your apartment until the sql-y haze starts to lift and you stop thinking it’s funny that you can’t GROUP BY the contents of your fridge.
Once you’ve collected yourself, you start the real process of investigation: opening, skimming, and rejecting stack overflow threads until you find one with a code block that looks similar enough to what you’re working on.
postgres 42803. check_ungrouped_columns_walker. filter query by join aggregate results without removing aggregate results. use alias in WHERE clause. how does sql look like. WHERE am I?
This is the second trip around the spiral. It can last anywhere from half an hour to an entire afternoon depending on how specific your problem is, but it ends when every result for every search term you can think of is purple. Every. Damn. Link. DuckDuckGo and Google. You’re on your own, sliding out of control into your third orbit around what you thought was going to be an easy fix.
You start to question your sanity. Or at least your competence. You start reading w3schools posts on how to write a WHERE clause, because you clearly don’t even understand how basic sql queries work. You move on to psql docs, fighting to keep your eyes from glazing over after 8 pedantic paragraphs of detailed caveats to using DISTINCT in joins. After slogging through every possibly relevant page of the docs, you even consider reading threads in the postgres community support forum.
The fourth orbit starts when you realize you should have moved on to something else hours ago. You’ve lost sight of the original problem and your back hurts. The little, wise human part of your brain is telling you to have dinner and come back to it tomorrow. The little frantic code monkey in your head is telling you that you have to figure this out before you can unplug. You keep sliding around the spiral, attempting one last solution you found on some Azeri guy’s blog from 2013. It doesn’t work, obviously, and the fifth spiral begins.
This time around, there is no code. No research, no reading. Just you and the problem.
You try to let it go, but it haunts you all night. Dreams come formatted by json_agg and you stare into the circadian void trying to find the answer to why sql syntax errors are so useless.
The sixth orbit is the tightest orbit. It begins the next day when you open your editor and figure out the solution immediately. You put your aggregation in a WITH clause, filter the results with the same WHERE clauses you had written before this all started, and commit. 30 minutes, start to finish. Including tests.
It should be a valuable lesson. A fresh mind is always better than a mind pickled in frustration and coffee. You try to recall everything you learned about aggregation and subqueries and translating psql docs to English. Maybe, someday, that latent knowledge will find its way into your code. But for now the best your frazzled mind can do is write a blog post about the process and hope you remember to read it the next time a query fails and you think “that’s weird… it should be working.”