Couple of months ago I wrote how to do human sort in Pg by using arrays, and splitting string.
This works, but Matt mentioned in comments that it could be done with ICU collations.
So I looked into it …
Continue reading Human/version sort in PostgreSQL – revisited
Ever been in situation where you had to sort data that is partially text, and partially numerical?
Like invoice numbers: prefix-9, prefix-10, prefix-11, other-5, other-20 ? Normally you can't do order by as you will get them in wrong order: other-20 ⇒ other-5 ⇒ prefix-10 ⇒ prefix-11 ⇒ prefix-9. Can something be done with it?
This problem happened recently to couple of people on various Pg support channels, so I figured I can write a bit more about it, so that in future I have a place where I can refer people to 🙂
psql, the database client for PostgreSQL has, since forever, support for variables.
These let you write certain queries in a way that is safe even when getting params from “outside".
Let's see what can be done with it…
This question was asked at least twice on some support channel. Getting a row is trivial: select * from table where id = ?. But what about dependencies – the rows that this exported row references?
Decided to take a look at this task.
Continue reading How to get a row, and all of it's dependencies?
FZF is quite popular tool for fuzzy string finder. Very helpful for checking history of commands.
But, can I use it in psql?
Whenever I'm doing some testing I need sample data. Easiest way to do it is to generate data using some random/generate_series queries.
But what if I need specific frequencies?
For example, I need to generate 10,000,000 rows, where there will be 10% of ‘a', 20% of ‘b', and the rest will be split equally between ‘c' and ‘d'?
Just so that it will be perfectly clear: the logs I have in mind are the ones for DBAs to read – with slow queries, errors, and other interesting information.
So, how does one find them?
Previously I wrote about locating config files.
The thing is – postgresql.conf is not the only place you can set your configuration in.
In here, I'll describe all the places that can be used, why do we even have more than one place, and finally – how to find out where given value comes from.
Continue reading Starting with Pg – where/how can I set configuration parameters?