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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Adrian Klaver Sent: October-21-14 18:58 To: Daniel Begin; pgsql-general@xxxxxxxxxxxxxx Subject: 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.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
