Word of warning: this blogpost is about thing related to Bash (well, maybe other shells too, didn’t really test), but since I found it while doing Pg work, and it might bite someone else doing Pg related work, I decided to add it to “postgresql” tag. So, due to some work I had to do, … Continue reading “A tale of automating tests of Pg with Bash”
Last time I wrote about what explain output shows. Now I’d like to talk more about various types of “nodes” / operations that you might see in explain plans.
One of the questions that gets asked quite a lot, is: how can you use variables in your queries? Other databases have them, does PostgreSQL? Actually the answer is “no”. But, it is actually very easy to work around, and what’s more important – quite often, what you need are not variables in queries, but … Continue reading “Variables in SQL, what, how, when?”
So, you just installed your PostgreSQL, and you have no idea how to use it – there is no icon in the menu of your OS, so how can you use it? Well, with the dreadful command line. Of course – some people will never get used to textual programs. They need a GUI. That’s … Continue reading “Command line tools? In XXI century? No way! Yes way!”
In PostgreSQL 8.4 we got CTE – Common Table Expressions. Since then we have this great tool available, but apparently for some people it’s still black magic. CuTE, but still magic. I’ll try to make it a bit less magical, and more understandable.
Some of you might be familiar with pgBouncer project. Some are not. Some understand what/how/why it does, others do not. This blog post is to have a place where I can point people who have question about how it works, why, and when it makes sense to use it (pgBouncer that is).
And maybe not on the wall, but instead in your SQLz, eating your data. But a bit more seriously. Ever since PostgreSQL 8.4 we have window functions, but still I see people which do not know it or are wary to use it. That’s why I decided to write a piece on window functions. How … Continue reading “Window, window on the wall …”
Some people are afraid of triggers. Reasons for this are not really understandable for me, but I guess it stems from the fact that these are usually application developers, and not database admins. Or they encountered some kind of problem with triggers, and now they tend to think that triggers are inherently evil. But they … Continue reading “How I Learned to Stop Worrying and Love the Triggers”
Yesterday on irc someone asked: Hi, how do I get top 5 values from a column group by another column?? From further discussion, I learned that: total rows in table is 2 million. It’ll have unique words of less than 1 million.. (approx count) I didn’t have time yesterday, but decided to write a solution, … Continue reading “Getting top-N rows per group”
Similar question has been asked many times on mailing lists and on IRC. Sometimes it’s not mail sending, but file/directory creation, or something else that generally requires some interaction with “world outside of database”. Can it be done? Sure. How, then?