Re: Inconsistency between PgAdmin III GUI and SQL window ?

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Adrian Klaver
Sent: October-22-14 09:46
To: Daniel Begin; 'David G Johnston'
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general