Inconsistency between PgAdmin III GUI and SQL window ?

[Daniel Begin]

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?

Daniel

[David G Johnston]

Post by Daniel Begin
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?

The geography type is not native to PostgreSQL and so does not exist in the
pg_catalog schema - which is the only schema that you get implicitly. If
you set a new search_path and want to see the geography type you have to
include the schema into which you installed it in the search_path
specification.

Assuming you installed it into "postgis" you would need:

SET search_path = xxx, postgis;

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Inconsistency-between-PgAdmin-III-GUI-and-SQL-window-tp5823862p5823868.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[Adrian Klaver]

Post by Daniel Begin
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'.

Post by Daniel Begin
Daniel

[Daniel Begin]

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-***@postgresql.org
[mailto:pgsql-general-***@postgresql.org] On Behalf Of Adrian Klaver
Sent: October-21-14 18:58
To: Daniel Begin; pgsql-***@postgresql.org
Subject: Re: [GENERAL] Inconsistency between PgAdmin III GUI and SQL window
?

Post by Daniel Begin
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'.

Post by Daniel Begin
Daniel

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

[Adrian Klaver]

Post by Daniel Begin
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

Post by Daniel Begin
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