Trouble installing additional module cube in PostgreSQL 8.4

Question

I am trying to use PostgreSQL with the "Seven Databases in Seven Weeks" book. I am using PostgreSQL 8.4.1 on an Ubuntu 10.04 server.

The first task is to create a database named "book" and check if the contrib packages have been installed properly.

$ createdb book
$ psql book -c "SELECT '1'::cube;"

When I do that I get the following output:

ERROR:  type "cube" does not exist
LINE 1: SELECT '1'::cube;

I already installed the cube package with the following command:

$ sudo -u postgres psql postgres < /usr/share/postgresql/8.4/contrib/cube.sql

I tried restarting PostgreSQL but the problem persists. When I tried running the package import a second time I got the following message, which explicitly states that type "cube" already exists:

SET
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
ERROR:  type "cube" already exists
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
COMMENT
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
ERROR:  operator < already exists
ERROR:  operator > already exists
ERROR:  operator <= already exists
ERROR:  operator >= already exists
ERROR:  operator && already exists
ERROR:  operator = already exists
ERROR:  operator <> already exists
ERROR:  operator @> already exists
ERROR:  operator <@ already exists
ERROR:  operator @ already exists
ERROR:  operator ~ already exists
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
ERROR:  operator class "cube_ops" for access method "btree" already exists
ERROR:  operator class "gist_cube_ops" for access method "gist" already exists

So, what am I doing wrong?

Answer 1

You only installed the extension to your postgres database (the default system database named "postgres") - which is probably not what you want. You need to install the extension to your database - once per database in which to use it.

Or you can install it to a template database (template1 by default, but any database can be used as template) so that every new database created starts out with the functionality pre-installed.

In PostgreSQL 8.4 or older, you need to run in the shell:

psql -d dbname -f SHAREDIR/contrib/cube.sql

Where dbname is the name of your actual target db. Or use the equivalent line that you have in your question.
More info for PostgreSQL 8.4 in the manual here.

Since PostgreSQL 9.1 this has been further simplified and you can just run in a database session:

CREATE extension cube

More in the manual here.