Re: Inconsistency between PgAdmin III GUI and SQL window ?

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.

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

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Adrian Klaver
Sent: October-21-14 18:58
To: Daniel Begin; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Inconsistency between PgAdmin III GUI and SQL window
?

On 10/21/2014 03:33 PM, Daniel Begin wrote:
> I have first to admit the inconsistency is probably on my side!-)
>
> The task - I want to clone a table I created in public schema in
> another schema (xxx) of the same database.
> The problem - I get an error message when creating the table using the
> original SQL script: ERROR: type "geography" does not exist.
>
> I understand from
> "http://stackoverflow.com/questions/9067335/how-to-create-table-inside
> -speci fic-schema-by-default-in-postgres" that the original script
> should work if I set the search path to the destination schema (xxx)
> prior to execute the script (set search_path to xxx ;) but the PgAdmin
> III SQL window does not seem aware of the geography type.
>
> What I do not understand is that using the GUI (contextual menu. new table
.
> new column.) within schema xxx, I can create the table as expected,
> and the geography type is recognized. Any idea about what is going on
> when using the SQL window?

A quick test here showed that in the GUI New Object(table) wizard the types
are schema qualified if needed. For instance I did:

CREATE TYPE public.test_type AS (my_int int);

When I went to another schema and created a table and then a column, the
test_type was shown as public.test_type and I could use it as the column
type. The table was created with no problem. So pgAdmin searches the schemas
for you to retrieve types when you use the creation wizard.

When you are using the SQL window it has no context other then what is
provided by the search_path. If the search_path does not include the schema
that holds the type you want, then you will get an error.

Probably the best way to see this is tail the Postgres log file where the
postgresql.conf file has log_statement = 'mod' or 'all'.

>
> Daniel
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

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