postgresql

Postgresq error: type 'hstore' does not exist

Today I was programming and messed up my database schema in such a way that I just wanted to restore a recent backup and start over from there. dropdb app_development createdb app_development Unfortunately I ran into an error: PostgreSQL error: type ‘hstore’ does not exist hstore is a Postgresql extension (available since postgresql-9.x) that allows you store sets of key/value pairs within a single Postgresql value (read column).

Rails migrations: decimal precision and scale

I’m always confused when using decimal in a Rails migration. Normally I need to store a value that has 2 or 3 numbers behind the comma (or dot), or decimals. Let’s say you have a Product model with a discount_percentage attribute. This attribute is currently an integer, only allowing non-decimal values. To allow 2 digit decimal values (e.g. 12.54), you can mak the following migration: change_column :products, :discount_percentage, :decimal, precision: 5, scale: 2 This will allow you to store values like 80.

Upgrade postgresql-8.4 to postgresql-9.1 on debian

Today I upgraded a production PostgreSQL 8.4 database to version 9.1. This was on a Debian server. ~ The first step is to make a full dump of your data. I personally like to store that dump somewhere safe before upgrading. As root: su - postgres pg_dumpall > dump.sql exit cp ~postgres/dump.sql /root/ Now you can safely remove the postgresql-8.4 and install postgresql-9.1: aptitude purge postgresql-8.4 aptitude install postgresql-9.1 Next check the postgresql configuration in /etc/postgresql/9.