FZF is quite popular tool for fuzzy string finder. Very helpful for checking history of commands.
But, can I use it in psql?
Tag: irc
Stop the war in Ukraine
Since yesterday my sites (explain.depesz.com, paste.depesz.com, and this blog) are no longer available from IPs that are in Russia or Belarus.
I know this is not huge loss for them, but, I think, every single bit counts.
If you'd like to do the same thing for their site/service:
Fixes on why-upgrade.depesz.com
In case you're not familiar with this site, why-upgrade.depesz.com shows you aggregated changelog between any two releases, with optionally searching for some keywords.
Yesterday azeem on irc pointed me towards a problem on why-upgrade.depesz.com. Specifically, when displaying changes from 9.5.24 to 13.1 site showed 30 security fixes. But in reality there should be only 10.
Fix for displaying aggregates on explain.depesz.com
Couple of days ago RhodiumToad reported, on irc, a bug in explain.depesz.com.
Specifically – if explain was done using JSON/XML/YAML formats, and node type was Aggregate, the site didn't extract full info.
Continue reading Fix for displaying aggregates on explain.depesz.com
Migrating simple table to partitioned. How?
Recently someone asked, on irc, how to make table partitioned.
The thing is that it was supposed to be done with new partitioning, and not the old way.
The problem is that while we can create table that will be seen as partitioned – we can't alter table to become partitioned.
So. Is it possible?
Continue reading Migrating simple table to partitioned. How?
Converting list of integers into list of ranges
Yesterday someone on irc asked:
i've a query that returns sequential numbers with gaps (generate_series + join) and my question is: can is somehow construct ranges out of the returned values? sort of range_agg or something?
There was no further discussion, aside from me saying
sure you can. not trivial task, but possible.
you'd need window functions.
but it got me thinking …
Continue reading Converting list of integers into list of ranges
Waiting for PostgreSQL 10 – Implement syntax for transition tables in AFTER triggers.
Another one missed, quite a long time ago, too..:
On 4th of November 2016, Kevin Grittner committed patch:
Implement syntax for transition tables in AFTER triggers. This is infrastructure for the complete SQL standard feature. No support is included at this point for execution nodes or PLs. The intent is to add that soon. As this patch leaves things, standard syntax can create tuplestores to contain old and/or new versions of rows affected by a statement. References to these tuplestores are in the TriggerData structure. C triggers can access the tuplestores directly, so they are usable, but they cannot yet be referenced within a SQL statement.
Getting list of unique elements in table, per group
Today, on irc, someone asked interesting question.
Basically she ran a query like:
SELECT a, b, c, d, e, f FROM TABLE ORDER BY a
then, she processed the query to get, for each a array of unique values of b, c, d, e, and f, and then he inserted it back to database, to some other table.
It was a problem, because the table had many rows (millions I would assume), and the whole process was slow.
So, how to make it faster?
Continue reading Getting list of unique elements in table, per group
Incrementing counters in database
Some time ago someone on irc asked about creating fast counters for something (banners I think).
I talked with her (him?) about it, but figured, I can as well write a blogpost, so others can use it too.
Waiting for 9.6 – Add num_nulls() and num_nonnulls() to count NULL arguments.
On 5th of February, Tom Lane committed patch:
Add num_nulls() and num_nonnulls() to count NULL arguments. An example use-case is "CHECK(num_nonnulls(a,b,c) = 1)" to assert that exactly one of a,b,c isn't NULL. The functions are variadic, so they can also be pressed into service to count the number of null or nonnull elements in an array. Marko Tiikkaja, reviewed by Pavel Stehule
Continue reading Waiting for 9.6 – Add num_nulls() and num_nonnulls() to count NULL arguments.