Nesting group by in postgres

Since my times using Crystal Reports (around 2005) I've been used to "a group by flattens all your columns into one value".  It was something inescapable (unless big hacks)

But lately, with complex types in postgres, and smarter aggregation functions, I've grown to create nested structures in group bys when I want to retain information about individual records inside the group.

It probably violates a few normalization rules, but  who cares?

So here's it:

select country, 
       count(*), 
       jsonb_pretty(jsonb_agg((id, status, deleted_at) order by deleted_at desc)) as statuses 
from customer 
group by country having count(*)>1 
order by count desc;

I think it's super cool to be able to generate a result with all the nested information of the integrants of each group, and even more, being able to order by, inside the groups!

I imagine there's a lot of cool stuff you can do later with UNNEST-style functions, that would allow us to recreate the registers as tables, to do further queries.

Or maybe this is just another hack, like the ones I was writing in 2005. But anyhow, they are very useful for data explorations.

Happy hacking

Just use Postgres for everything!

 Just discovered this post https://www.amazingcto.com/postgres-for-everything/ which is short, and to the point.

Copying it verbatim, because it deserves to be read even if you don't click my links

"

One way to simplify your stack and reduce the moving parts, speed up development, lower the risk and deliver more features in your startup is “Use Postgres for everything”. Postgres can replace - up to millions of users - many backend technologies, Kafka, RabbitMQ, Mongo and Redis among them.

Use Postgres for caching instead of Redis with UNLOGGED tables and TEXT as a JSON data type. Use stored procedures to add and enforce an expiry date for the data just like in Redis.

Use Postgres as a message queue with SKIP LOCKED instead of Kafka (if you only need a message queue).

Use Postgres with Timescale as a data warehouse.

Use Postgres with JSONB to store Json documents in a database, search and index them - instead of Mongo.

Use Postgres as a cron demon to take actions at certain times, like sending mails, with pg_cron adding events to a message queue.

Use Postgres for Geospacial queries.

Use Postgres for Fulltext Search instead of Elastic.

Use Postgres to generate JSON in the database, write no server side code and directly give it to the API.

Use Postgres with a GraphQL adapter to deliver GraphQL if needed.

There I’ve said it, just use Postgres for everything.

"

Also, there's a link to radical simplicity, which seems like another iteration of use-boring-technology.

This one is also great gist about using pg for basically everything https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb

Idempotency is so great

Once I'm presented with a problem, there are a few angles I instantly look at it, to get a shape of which kinds of solutions might work, and whether the problem allows for unknowns as I go, or it has to be right.

- One of them is idempotency. 

- Another is "does it allow for layered development/deployment?". Can I keep figuring out the rest of the owl as I'm drawing it?

- Steel thread vs gaining resolution as we go through

I remember Christopher Alexander's

The difference between the novice and the master is simply that the novice has not learnt, yet, how to do things in such a way that he can afford to make small mistakes. The master knows that the sequence of his actions will always allow him to cover his mistakes a little further down the line. It is this simple but essential knowledge which gives the work of a master carpenter its wonderful, smooth, relaxed, and almost unconcerned simplicity.

https://ericlathrop.com/2021/04/idempotence-now-prevents-pain-later/

https://news.ycombinator.com/item?id=31027403

https://www.berkansasmaz.com/every-programmer-should-know-idempotency/

https://newsletter.casewhen.xyz/p/data-explained-idempotence

https://www.youtube.com/watch?v=GRr4xeMn1uU

https://lightfoot.dev/why-arent-you-idempotent/

https://danluu.com/simple-architectures/ (links to some brandur posts about idempotency and jobs)

A Few C++ Talks?

 Here are a few talks from the C++ community that when I watched I thought I'd rewatch again because they either are very cool even though I don't grok C++, or they talk about general programming concepts. Here's the list:

- https://www.youtube.com/watch?v=V5SCJIWIPjk Optimizing for Change in C++ - Ben Deane

- https://www.youtube.com/watch?v=2ouxETt75R4 Easy to use, hard to missuse - Ben Deane

- https://www.youtube.com/watch?v=sWgDk-o-6ZE CppCon 2015: Sean Parent "Better Code: Data Structures"

- https://www.youtube.com/watch?v=W2tWOdzgXHA&list=PLM5v5JsFsgP21eB4z2mIL8upkvT00Tw9B Sean Parent "Seasoning" talking about algorithms

- https://www.youtube.com/watch?v=JELcdZLre3s Function Composition in Programming Languages - Conor Hoekstra - CppNorth 2023

Naive option picker (fzf) in pure shell

 I've been a big fan of option pickers since.... forever. I've used ratmenu, ratmen, dmenu, dzen, percol, fzf, helm, vertico, consul...

For me, it's so fundamental that I need this functionality in every piece of code/script I write.

Finally I got to a working version of a very small version of it.

As always, pure shell, but with some advanced features can give you a succint function that works predictably.  The /dev/tty part was the trickiest to get working, as I didn't know how to get input from a pipe, and at the same time be able to read data from the keyboard.

Yet another example of "code the stupidest thing first"

 I've read a post called "Code the shortest path first", and I couldn't agree more, really. The core idea is a well known one to the reader of this blog (me, mostly). It's not even about YAGNI, or "The Wrong Abstraction". It's about having that "tracer bullet" or "steel thread" that guides you through the implementation phases. Also, if the feature looks too big from the beginning, you can try "SPIDR".

Also, what I find is that my first code, it's usually highly compressed, and that also gives a first soul to the implementation that lives on during the implementation. 

postgres jsonb_diff and array_unique

 I recently copypasted a couple of functions from stackoverflow for my prostgres projects that are worth mentioning, cause they show a pattern on how to write plpsql functions: