Thank Adrian, I just found what went wrong in my script... As described in the initial email, I set the search path to the destination schema (xxx) prior to execute the script. Doing so, I was excluding the public schema from the search and then cannot have access to PostGIS extension. By setting the search path to xxx AND public schema (set search_path to xxx, public;) everything goes right! Daniel -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Adrian Klaver Sent: October-22-14 09:46 To: Daniel Begin; 'David G Johnston' Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: Inconsistency between PgAdmin III GUI and SQL window ? On 10/22/2014 03:25 AM, Daniel Begin wrote: > David, Adrian, > I am new to databases and since PgAdmin displays Catalogs, Event > Triggers, Extensions and Schema as "Child" of the database, I assumed > that Extensions were linked to a specific database, not to a specific schema. Yes and no. If they are installed in the pg_catalog schema then they are available database wide: http://www.postgresql.org/docs/9.3/static/ddl-schemas.html In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables and all the built-in data types, functions, and operators. pg_catalog is always effectively part of the search path. If it is not named explicitly in the path then it is implicitly searched before searching the path's schemas. This ensures that built-in names will always be findable. However, you can explicitly place pg_catalog at the end of your search path if you prefer to have user-defined names override built-in names. This is where you will find the procedural languages. So in psql you can do the below to see what is installed and where: test=# \dx List of installed extensions Name | Version | Schema | Description -----------+---------+------------+------------------------------------- -----------+---------+------------+----------------------- hstore | 1.2 | public | data type for storing sets of (key, value) pairs plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language tablefunc | 1.0 | public | functions that manipulate whole tables, including crosstab > > After reading your answers, I had another look at PostGIS extension > properties and it is pretty clear it belongs to the public schema, > which explains the error message I got. > > Thanks > Daniel > -- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
