Someone asked today on irc about grouping data, that contains timestamps, into “partitions".
Usually when someone wants something like this, you can do grouping by date_trunc(), but this time, this person, wanted to group data that all timestamps are within given interval from each other.
I'm not sure I understood him/her right, but I think he/she wanted something like this:
Some time ago someone on irc asked interesting question. One that I couldn't answer then (didn't have an immediate idea, and didn't have time to spend on looking into it).
Now, I have some more time, and despite the fact that the person that had this problem no longer cares about it (he found some solution himself if I recall correctly), decided to look into it.
Yesterday I had an interesting discussion on irc.
A guy wanted to know why Perl script is causing problems when dealing with Pg
and unicode characters.
The discussion went sideways, I got (a bit) upset, and had to leave anyway, so
I didn't finish it. But it did bother me, as for me the reasons of the problem
seem obvious, yet the person I talked with was very adamant that I have the
whole thing wrong.
So, I figured I'll use my blog to elaborate a bit…
Continue reading PostgreSQL + Perl + Unicode == confusion. Why?
Couple of days ago I had a problem that I couldn't solve after ~ 2 hours, and decided to ask on IRC. Almost immediately after asking, I figured out the solution, but David asked me to write about the solution, even though it's now (for me) completely obvious.
The problem was like this:
I had two tables, with very simple structure: event_when timestamptz, event_count int4, and wanted to show it as a single recordset with columns: event_when, event_count_a, event_count_b, but the problem was that event_when usually didn't match. Here is an example:
Some guy came to #postgresql today. Described his problem, got additional question, and then … well .. I wouldn't believe if it didn't happen to me.
Continue reading How to make sure you will not get any help on IRC
Today, there was this one person on IRC, which asked question and provided some data. While working on helping him (her?), I noticed some things, that bugged me before in other cases, but this time i decided to write about it – it's kind of rant, and if you (the reader) are the person that I'm basing my example on – please do not feel “punished" – it just so happens, that you exhibited some things that make helping others more difficult than it could be – so: you're not special, although I would really prefer if you were 🙂
Continue reading What mistakes you can avoid when looking for help on IRC?
Dynamic updates of fields in NEW in PL/pgSQL
Today, on #postgresql on IRC, strk asked about updating fields in NEW record, in plpgsql, but where name of the field is in variable.
After some time, he sent his question to hackers mailing list. And he got prompt reply that it's not possible.
Well, I dare to disagree.
Continue reading Stupid tricks – Dynamic updates of fields in NEW in PL/pgSQL
Today, on irc (#postgresql on freenode.net) Dim mentioned about writing median calculation code.
It got me thinking, and consequently writing my version of median calculation code.
Today, on #postgresql on IRC, guy (can't contact him now to get his permission to name him), said:
I have a table called problematic_hostnames. It contains a list of banned hostnames in column “hostname" (varchar). I would like to display the top 10 troll ISPs based on this. Does PG have a way of spotting a “pattern"? Some ISPs are example.net while others are foo.bar.example.net, so you can't just regexp the last X.Y (since that would cause “.co.uk" to be one of the top troll ISPs).