Re: 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