Thanks for getting back to me. Still no luck; and I tried all possibilities..
For example, when I tried on new DB:
db1=# CREATE EXTENSION hstore
SCHEMA public
VERSION "1.0";
ERROR: type "hstore" already exists
db1=# create table foo(id hstore);
ERROR: type "hstore" is only a shell
LINE 1: create table foo(id hstore);
^
db1=# create table foo(id public.hstore);
ERROR: type "public.hstore" is only a shell
LINE 1: create table foo(id public.hstore);
db1=# SELECT * FROM pg_available_extension_versions
engine_db-# WHERE name = 'hstore';
-[ RECORD 1 ]-------------------------------------------------
name | hstore
version | 1.0
installed | f
superuser | t
relocatable | t
schema |
requires |
comment | data type for storing sets of (key, value) pairs
db1=# SELECT * FROM
pg_extension e INNER JOIN pg_namespace n ON (e.extnamespace = n.oid);
-[ RECORD 1 ]--+-----------------------------------
extname | plpgsql
extowner | 10
extnamespace | 11
extrelocatable | f
extversion | 1.0
extconfig |
extcondition |
nspname | pg_catalog
nspowner | 10
nspacl | {postgres=UC/postgres,=U/postgres}
But if I use it in other DB, where it was installed; it works fine..
db2=# SELECT * FROM
pg_extension e INNER JOIN pg_namespace n ON (e.extnamespace = n.oid);
-[ RECORD 1 ]--+-----------------------------------
extname | plpgsql
extowner | 10
extnamespace | 11
extrelocatable | f
extversion | 1.0
extconfig |
extcondition |
nspname | pg_catalog
nspowner | 10
nspacl | {postgres=UC/postgres,=U/postgres}
-[ RECORD 2 ]--+-----------------------------------
extname | hstore
extowner | 10
extnamespace | 2200
extrelocatable | t
extversion | 1.0
extconfig |
extcondition |
nspname | public
nspowner | 10
nspacl | {postgres=UC/postgres,=U/postgres}
db2 =# SELECT * FROM pg_available_extension_versions
WHERE name = 'hstore';
-[ RECORD 1 ]-------------------------------------------------
name | hstore
version | 1.0
installed | t
superuser | t
relocatable | t
schema |
requires |
comment | data type for storing sets of (key, value) pairs
any other hints ?
________________________________
From: Bartosz Dmytrak <bdmyt...@eranet.pl>
To: Brian Trudal <dbrb2002-...@yahoo.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Tuesday, March 6, 2012 1:02 PM
Subject: Re: [GENERAL] 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-...@yahoo.com>
Any one know how to install extensions to multiple databases in the same server
?
>
>Thanks in advance
>Brian
>
>
>________________________________
> From: Brian Trudal <dbrb2002-...@yahoo.com>
>To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.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);
> ^
>
>
>
