Re: Single server multiple databases - extension - Mailing list pgsql-general

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 ?