Re: Single server multiple databases - extension

Hi,
there shouldn't be any problem in installing extensions to multiple
databases in the same server. Extensions are per database:
http://www.postgresql.org/docs/9.1/static/sql-createextension.html

You can use pgAdmin, or try this syntax:
CREATE EXTENSION hstore
SCHEMA public
VERSION "1.0";

if hstore is installed in public schema, sometimes You have to use
public.hstore syntax (fully qualified name) - this depends on your
search_path setting.

in your example it looks like hstore is installed, but question is: where
is it?

You can find this info using SQL like this one:
SELECT * FROM
pg_extension e INNER JOIN pg_namespace n ON (e.extnamespace = n.oid)
http://www.postgresql.org/docs/9.1/static/catalog-pg-extension.html

this could be useful too:
SELECT * FROM pg_available_extension_versions
WHERE name = 'hstore'
http://www.postgresql.org/docs/9.1/static/view-pg-available-extension-versions.html

Regards,
Bartek

2012/3/6 Brian Trudal <dbrb2002-sql(at)yahoo(dot)com>

> Any one know how to install extensions to multiple databases in the same
> server ?
>
> Thanks in advance
> Brian
> ------------------------------
> *From:* Brian Trudal <dbrb2002-sql(at)yahoo(dot)com>
> *To:* "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
> *Sent:* Monday, March 5, 2012 4:52 PM
> *Subject:* Single server multiple databases - extension
>
> Hi
>
> I have 2 databases running in a single server; and I installed extension
> 'hstore' to one database and it works fine. When I tried to use the same
> extension in another database, it gives an error saying 'extension does not
> exist'; nor it allow to install as it complains about its existence.
>
> Any help ?
>
> db1=# CREATE EXTENSION hstore;
> ERROR: type "hstore" already exists
> db1=# DROP EXTENSION hstore;
> ERROR: extension "hstore" does not exist
> db1=# create table foo(id hstore);
> ERROR: type "hstore" is only a shell
> LINE 1: create table foo(id hstore);
> ^
>
>
>