20 December 2010
I needed to make slugs in Postgresql from names which included diacritics. Though not complete, here’s a reasonable stab at it:
select regexp_replace(translate(replace(lower(your_field_name_here), ' ', '-'), 'áàâãäåāăąÁÂÃÄÅĀĂĄèééêëēĕėęěĒĔĖĘĚìíîïìĩīĭÌÍÎÏÌĨĪĬóôõöōŏőÒÓÔÕÖŌŎŐùúûüũūŭůÙÚÛÜŨŪŬŮ','aaaaaaaaaaaaaaaaaeeeeeeeeeeeeeeeiiiiiiiiiiiiiiiiooooooooooooooouuuuuuuuuuuuuuuu'), '[^\w -]', '', 'g') as slug
The translate is not quite right because it translates some uppercase to lowercase, but it’s all lowercase for the slug, so it doesn’t matter here.
[Update] Added ‘g’ flag to regexp_replace to replace all occurrences, not just first.
23 June 2009
When you upgrade Postgresl to a new major version (e.g. 8.1 to 8.3), all databases need to be dumped from the old version and loaded in to the new version. It’s not difficult, but on Debian there’s a really easy way.
pg_upgradecluster (plus a few others). The one I’m referring to here is
It takes the version and cluster name of the databases you want to upgrade.
e.g. if you’ve installed Postgresql 8.3 and have databases in 8.1, just run:
# pg_upgradecluster 8.1 main
This upgrades the databases in the “main” cluster under version 8.1 and puts them under “main” in version 8.3. If you already have a “main” cluster in 8.3, you’ll need to drop it first.
This little tool not only dumps and loads the database, but it also changes the config so 8.3 runs on the standard port previously used by 8.1 (or whatever your older version). A painless way to upgrade.
Comments Off on Migrating Postgresql Databases the Easy Way