I have a lovely PostgreSQL 9.0 server installed on my laptop via MacPorts. I would like to enable the hstore module, but I can't find any instructions for installing these optional modules (nor can I find any hstore-related code in
I have found some hstore-related SQL here, but I'm not sure where it comes from or if it's compatible w/ PostgreSQL 9.0.
So, how do I enable the hstore module on my MacPorts-installed Postgres 9.0 server?
You can tell MacPorts to build hstore. Here's how.
If you already have
postgresql installed, you will need to uninstall it first (this won't touch your data or users) because the
install action will not re-install an already installed port. The uninstall is forced (
postgresql91-server is dependent and will prevent uninstall.
sudo port -f uninstall postgresql91
Edit the Portfile and add
hstore to the list on the line which begins with
sudo port edit postgresql91
(Re)install from source explicitly (
-s) to build the hstore extension:
sudo port -s install postgresql91
Then load hstore, once for each of your databases in which you want to use it:
In >= 9.1:
CREATE EXTENSION hstore;
psql -U postgres -f /opt/local/share/postgresql90/contrib/hstore.sql
Note this process works for postgresql92 by just substituting "92" for "91".
It seems that the port for PostgreSQL 9.1 now includes hstore, but it still needs to be enabled. Install and start the database normally.
sudo port install postgresql91 postgresql91-server sudo mkdir -p /opt/local/var/db/postgresql91/defaultdb sudo chown postgres:postgres /opt/local/var/db/postgresql91/defaultdb sudo su postgres -c '/opt/local/lib/postgresql91/bin/initdb \ -D /opt/local/var/db/postgresql91/defaultdb' sudo port load postgresql91-server
EDIT: Installing in another computer didn't work as well. The hstore was not installed with the base (I may have made it available trying other solutions). So do this BEFORE the load command above:
sudo port unload postgresql91-server # if you did load above sudo port build postgresql91 port work postgresql91 # Gives you base dir for following command cd /opt/local/var/macports/build/_opt_local_var_macports_sources_rsync.macports.org_release_ports_databases_postgresql91/postgresql91/work/postgresql-9.1.*/contrib/hstore sudo make all sudo make install clean sudo port load postgresql91-server
To enable the hstore extension, use the new "create extension" SQL command in the database(s) you will use hstore. If you install it into the template1 database, all databases created afterwards will have the hstore extension.
psql template1 postgres template1=# create extension hstore;
If you only need the extension in a particular database:
psql dbname dbuser dbname=# create extension hstore; create table a (id serial, data hstore); NOTICE: CREATE TABLE will create implicit sequence "a_id_seq" for serial column "a.id" CREATE TABLE dbname=# insert into a(data) values('a=>1, b=>2'); INSERT 0 1 dbname=# SELECT * from a; id | data ----+-------------------- 1 | "a"=>"1", "b"=>"2" (1 row)
I can't say for MacOS (or whatever MacPorts is), but on Windows there is a file "hstore.sql" in share/contrib and it references a library "hstore.dll" which is part of the regular distribution.
This was included in the one click installer from EnterpriseDB. I would assume that the one click installer for MacOS includes that module as well:
Joey Adam's solution is correct, but has become slightly dated with postgres 9.1:
I did the following differently from his post: