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?
On May, 12ve, Bruce Momjian committed new contrib module for 9.0 – pg_upgrage.
As I understand – this is what was available before as pg-migrator.
If you're not familiar with it – it's a tool that allows upgrade of $PGDATA from some version to some version. What's the use case? Let's assume you have this 200GB database working as 8.3, and you'd like to go to 8.4 (or 9.0). Normal way is pg_dump + pg_restore – which will take some time. With pg-migrate/pg_upgrade it should be faster, and easier. So, let's play with it.