Обсуждение: Inconsistency between PgAdmin III GUI and SQL window ?
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
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? 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.
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@aklaver.com
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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Adrian Klaver Sent: October-21-14 18:58 To: Daniel Begin; pgsql-general@postgresql.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.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
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
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