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

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:

Copy table from another db in postgres

 This one is somehow a followup on "Duplicate row in plain sql". What I needed in this case was to copy 

Both dbs live in the same physical postgres, and the main user has rights over everything, so the process was painless, and took 5 minutes to set it up (accounting for reading the docs).

The trick is to use the postgres_fdw and create a schema "dev" inside db_test, so I can access the tables in db_dev.

Now you can easily do your  `insert into table_to_copy select * from dev.table_to_copy;`

Duplicate row in plain sql

 Another no-magic-here post, but quite a useful one when working with data in dev, and needing to massage it.

Have you found the need to duplicate a row, only with slight modification? SQL doesn't have a standard way to do it, and while some rdbms accept some sort of "SELECT * EXCEPT(id) FROM xyz", you might not have it available in your db (postgres)

The clearest solution I found is here:

It's all about creating a temporary table, and use "like" and "insert into", to avoid having to write all the fields:

CREATE TEMP TABLE tmp (like web_book);
INSERT INTO tmp SELECT * FROM web_book WHERE id = 3;
UPDATE tmp SET id = nextval('web_book_id_seq');
INSERT INTO web_book SELECT * from tmp;

In fact, we could simplify it further (at least in postgres) with:

CREATE TEMP TABLE tmp as SELECT * FROM web_book WHERE id = 3;
UPDATE tmp SET id = nextval('web_book_id_seq');
INSERT INTO web_book SELECT * from tmp;

"delete limit X" is not possible in postgres...

And how to fix it.

 

So I was pretty sure that I had done some sort of large-ish destructive (as in side-effect-y) operations in batches of small numbers.  And today I had to delete a bunch of rows from a bunch of tables.

It turns out that postgres doesn't support "LIMIT" in UPDATE nor DELETEs.

 First of all, the misunderstanding when I remembered doing destructive operations in batches comes from calling postgres functions via select.

Let's say you have to kill the db connections to a db:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='db_1234567890abcdef';

This, being a select, you can do things like:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname like 'db_%' limit 100;

So yes, you are batching operations, but it's because "selects" are not always side-effect-free.

So now, back to our real problem of doing updates or deletes in batches.

 It's not that it hasn't been asked or thought about. Here's one of the mail threads about it. Tom Lane's answer highlights some of the issues from the correctness perspective.

Ok, but if I really want to do it... what's the best way? Crunchydata has a blog post explaining multiple ways of doing it, but the main takeaway is to use CTEs to select the amount you want (using limit), and then delete by id "where exists" or "where id in".

WITH rows AS (
  SELECT
 something
  FROM
    big_table
  LIMIT 10
)
DELETE FROM big_table
WHERE something IN (SELECT something FROM rows);




Cool stuff. Nothing magical, but it's a good pattern to be familiar with.

SQL UNNEST to iterate over an array

I'm a very happy user of supa_audit library. This very small sql library allowed leverages some old postgres common knowledge about triggers and audits in a very nicely packed form, so you can track down changes to tables or rows in tables.

You can easily track changes in table "foo" just by running 

SELECT audit.enable_tracking('foo'::regclass)

I started with very naive way of tracking down objects, by identifying them via `table_name`+`record->>'id'`. That sort of works, but it doesn't scale at all, requiring full scans for everything, and detoasting `record` when the record is big enough.

supa_audit provides this `to_record_id` function that generates the indexed record_id value for a given row. What happens if we want to run it for 2 ad-hoc values?

Well, there's `unnest`, which is very cool. It takes an array, and converts it to a table-like relation, so you can use it as a `from`. That way, we could process N values in one sql go with the following:

select audit.to_record_id('tablename'::oid,'{id}', jsonb_build_object('id',id))::uuid as record_id from unnest(?)

Nice trick, right?

SQL window functions

SQL's window functions are such a huge topic that some see them as a DSL inside SQL itself.

You can do amazing things in sql without window functions, but when you need sophisticated rankings, or sorting subgroups, or you feel you are struggling with `group by + having + order by`, window functions are usually the answer.

 To me, Bruce Momjian's talk about window functions has been my default resource whenever I need a refresher.

But today I found a teaser of a book on window functions that's coming that looks very very good. I'll keep an eye on this page.  Also, the pic is from that site, so it deserves a link from my high-traffic blog (you're welcome).

Update primary key and associated foreign keys in sql

If you have two tables with a foreign_key relationship, and you want to update the value of a primary key, postgresql will fail, telling you that there are associated tables with foreign keys to that value, so you can't break the consistency.

update subscription set cus_id=2 where cus_id=1;

ERROR:  23503: update or delete on table "subscription" violates foreign key constraint "xxxxxx_id_fkey" on table "xxxxx"
DETAIL:  Key (id)=(1) is still referenced from table "xxxxx".
LOCATION:  ri_ReportViolation, ri_triggers.c:2797

Oh, I know what I'll do (you think), I'll change both inside a transaction:

begin; update subscription set cus_id=2 where cus_id=1;...
ERROR:  23503: update or delete on table "subscription" violates foreign key constraint "xxxxxx_id_fkey" on table "xxxxx"
DETAIL:  Key (id)=(1) is still referenced from table "xxxxx".
LOCATION:  ri_ReportViolation, ri_triggers.c:2797

Oh, the same :( So even inside a transaction, we can't have inconsistent states.

So.... The trick to let postgres update both columns of the two tables at once is to use a cte. Common Table Expressions have many different uses, and one of them is to bundle multiple queries in a single one. When used with update/inserts it allows these kinds of tricks.

with c as (update customer set id=42 where id=1),
        s as (update subscription set cus_id=42 where cus_id=1)
select 1;

As a_horse_with_no_name explains here:

As (non-deferrable) foreign keys are evaluated on statement level and both the primary and foreign key are changed in the same statement, this works.

SQL subselect arbitrary field from arbitrary table

Imagine you used to have `select f1, f2 from t1`. But now f2 becomes a fk to a table t2, so now we have f2_id that points to t2 (with fields id, name) where t1.f2_id = t2.id .

In my case, I only had access to the select fields list (the 'from t1' was already fixed, and I just could modify the retrieved fields), so I couldn't do an actual join, BUT! I found you can do a similar thing to an sql join for individual fields, adding a subquery as a field in the field_list.

select t1.f1, (select name from t2 where  t2.id=t1.f2_id) as f2 from t1 

Really really nice to know that t1.f2_id can be gathered from the subquery. The subquery will be triggered once per t1 row, so it'll probably be slower than a join, but check your EXPLAIN ANALYZE, and see if the cost is bearable, or even if it has any impact at all for your use case.

Some more explanations in here, or here.

Read-only psql

This is how I wrote a short snippet to open read-only postgres connection using psql.  Notice the amount of non-trivial stuff in those lines (technically, only one line of code).

 

I tried to show many concepts in those short snippets I post around here. 

• For example, notice how some lines need backslashes to continue the line, but some others not. 
• Also notice how to use <() to create a temporary file, so we don't have to have a duplicate file (probably outdated). 
• The command being a "cat originalfile && echo "customline") makes it a fresh file each time, that inherits from your originalfile. 
• Finally, good old "$@" to proxy the parameters to another command.

in zsh, you can add "compdef ropsql=psql" so it also proxies the autocompletion

Postgres AoC

Here's a link I don't want to lose, a thread about amazing people doing Advent of Code in Postgres. If you ask 'why', you're probably not the good audience for that anyway.

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

a few cli tools

 New job, new tooling, and reinventing the same trick again and again :)

This time, a few things on console stuff:

• https://github.com/okbob/pspg
• https://github.com/rcoh/angle-grinder
• https://github.com/jpmens/jo
  

Postgres 12 and a db course

Postgres 12 was recently released with some great features like concurrent reindex, jsonpath, and other stuff: https://www.postgresql.org/about/news/1976/  and https://www.postgresql.org/docs/12/release-12.html are good general info pages for the new stuff there.

Then, this is a great course about databases. It is about the internals of databases: https://www.youtube.com/watch?v=1D81vXw2T_w&list=PLSE8ODhjZXjbohkNBWQs_otTrBTrjyohi&index=1 . Really cool (at least the first 4 lectures).

Another sql thingie:
Julia Evans has this select order of execution that also is quite helpful if you're learning SQL https://news.ycombinator.com/item?id=21150606 .

EDIT: An advanced db course from Pavlo (great, more courses!): https://www.youtube.com/playlist?list=PLSE8ODhjZXja7K1hjZ01UTVDnGQdx5v5U

postgres indexes

I'm progressively more convinced PostgreSQL is my next "emacs", as in "a tool I'm addicted to and I'm determined to find out everything about it".

I'm starting with some of the internals, and some deep descriptions about its various types of indexes.  Those pages provided me a bunch of hours of reading and knowledge on postgres internals.

http://www.louisemeta.com/blog/indexes-gin/
https://habr.com/en/company/postgrespro/blog/441962/
http://www.interdb.jp/pg/

Also, this week I read somewhere that postgres was first written in Lisp! I guess it shows when you run EXPLAIN on a query. I just confirmed it for a fact. :)

Postgres compression of jsonb

Postgres supports JSON data since loong time ago, but until jsonb (9.4) all solutions were somewhat limited in features.  Jsonb gives you most of what a document storage engine would give you, but...  what about performance? and space efficiency? Unfortunately I don't have real answers to those questions, but I've started doing some research, and for now, here's a nice takeaway:

First of all you should be aware of "\dt+", which gives us the size of a table and "\d+" which describes a table. The "+" suffix adds more info to the descriptions.

CREATE TABLE foo AS SELECT '{"f":true}'::jsonb FROM generate_series(1,1e6);

CREATE TABLE bar AS SELECT '{"f":true}'::text FROM generate_series(1,1e6);

\dt+ foo;

\dt+ bar;  

We see that the text field takes about half of the size of the jsonb. So if you don't need json features in a field (you're only archiving data that happens to be json), think twice when giving it the jsonb type.


Jsonb is TOAST-able, but will probably be toasted  (and compressed) when it's bigger than a page, so don't count on that if you're jsons are <4kb .="" p="">
References:

• https://dba.stackexchange.com/questions/161864/do-long-names-for-jsonb-keys-use-more-storage
• https://stackoverflow.com/questions/23120072/how-to-check-if-toast-is-working-on-a-particular-table-in-postgres
• https://postgrespro.com/list/thread-id/1849114

So, don't think spacewise it comes for free. If you're concerned about space, there's a big impact on the size of the table.