Обсуждение: PGAdmin: type geography does not exist
PGadmin4.11
Ubuntu 18.04
Postgresql 11.4
I just tried to create the table below:
CREATE TABLE teleon.to_location (
locn_id serial NOT NULL,
geo_position geography(POINT, 4326) NOT NULL,
upd_time timestamp NOT NULL,
app_src_key char NOT NULL,
CONSTRAINT to_location_pk PRIMARY KEY (locn_id)
locn_id serial NOT NULL,
geo_position geography(POINT, 4326) NOT NULL,
upd_time timestamp NOT NULL,
app_src_key char NOT NULL,
CONSTRAINT to_location_pk PRIMARY KEY (locn_id)
I got :
ERROR: type "geography" does not exist LINE 5: geo_position geography(POINT, 4326) NOT NULL, ^ SQL state: 42704 Character: 166
Here is the strange thing: I run this in psql and it works fine.
I also have other tables in the system already defined with geography(POINT, 4326).
This isn't related to the 'No results in 4.11 Ubuntu thread?
Can you please let me know the output of 'SHOW search_path' from the psql?
On Tue, Aug 6, 2019 at 3:46 PM Tony Shelver <tshelver@gmail.com> wrote:
PGadmin4.11Ubuntu 18.04Postgresql 11.4I just tried to create the table below:CREATE TABLE teleon.to_location (
locn_id serial NOT NULL,
geo_position geography(POINT, 4326) NOT NULL,
upd_time timestamp NOT NULL,
app_src_key char NOT NULL,
CONSTRAINT to_location_pk PRIMARY KEY (locn_id)I got :ERROR: type "geography" does not exist LINE 5: geo_position geography(POINT, 4326) NOT NULL, ^ SQL state: 42704 Character: 166Here is the strange thing: I run this in psql and it works fine.I also have other tables in the system already defined with geography(POINT, 4326).This isn't related to the 'No results in 4.11 Ubuntu thread?
On Tue, 6 Aug 2019 at 12:18, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:
Can you please let me know the output of 'SHOW search_path' from the psql?
Ashesh, if I run SHOW search_path in psql, there is no output. Just returns with the prompt.
And - the following command for psql?
\dT geography
\dT geography
On Tue, Aug 6, 2019 at 4:02 PM Tony Shelver <tshelver@gmail.com> wrote:
On Tue, 6 Aug 2019 at 12:18, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:Can you please let me know the output of 'SHOW search_path' from the psql?Ashesh, if I run SHOW search_path in psql, there is no output. Just returns with the prompt.
Schema | Name | Description
--------+-----------+----------------------------------------------
teleon | geography | postgis type: Ellipsoidal spatial data type.
--------+-----------+----------------------------------------------
teleon | geography | postgis type: Ellipsoidal spatial data type.
(1 row)
On Tue, 6 Aug 2019 at 12:36, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:
And - the following command for psql?
\dT geographyOn Tue, Aug 6, 2019 at 4:02 PM Tony Shelver <tshelver@gmail.com> wrote:On Tue, 6 Aug 2019 at 12:18, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:Can you please let me know the output of 'SHOW search_path' from the psql?Ashesh, if I run SHOW search_path in psql, there is no output. Just returns with the prompt.
If you're running from the sql editor, then - you have either of the two options:
Set search_path = 'teleon, public';
CREATE TABLE...
Or,
Specify the schema in front of tge type.
-- Thanks, Ashesh
On Tue, 6 Aug 2019, 17:14 Tony Shelver, <tshelver@gmail.com> wrote:
Schema | Name | Description
--------+-----------+----------------------------------------------
| geography | postgis type: Ellipsoidal spatial data type.(1 row)On Tue, 6 Aug 2019 at 12:36, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:And - the following command for psql?
\dT geographyOn Tue, Aug 6, 2019 at 4:02 PM Tony Shelver <tshelver@gmail.com> wrote:On Tue, 6 Aug 2019 at 12:18, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:Can you please let me know the output of 'SHOW search_path' from the psql?Ashesh, if I run SHOW search_path in psql, there is no output. Just returns with the prompt.
To add more info to this,
types in postgres are schema bound. There is no knowing where the geography type is in your database. You might want to check all the schema to find where the type is, its in public by default unless you renamed public, and add it to the search path. Also, If you have some sort of multi tenancy system, you might want to use "$user" in the search path. So that other users don't have to prefix their queries.
SET search_path TO "$user",public;
On Tue, Aug 6, 2019 at 6:02 PM Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:
If you're running from the sql editor, then - you have either of the two options:Set search_path = 'teleon, public';CREATE TABLE...Or,Specify the schema in front of tge type.-- Thanks, AsheshOn Tue, 6 Aug 2019, 17:14 Tony Shelver, <tshelver@gmail.com> wrote:Schema | Name | Description
--------+-----------+----------------------------------------------
| geography | postgis type: Ellipsoidal spatial data type.(1 row)On Tue, 6 Aug 2019 at 12:36, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:And - the following command for psql?
\dT geographyOn Tue, Aug 6, 2019 at 4:02 PM Tony Shelver <tshelver@gmail.com> wrote:On Tue, 6 Aug 2019 at 12:18, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:Can you please let me know the output of 'SHOW search_path' from the psql?Ashesh, if I run SHOW search_path in psql, there is no output. Just returns with the prompt.
I'm not sure if the schema the extension is installed in is causing the problem. I always install extensions in the pg_catalog schema.
On Tue, Aug 6, 2019 at 7:32 AM Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:
If you're running from the sql editor, then - you have either of the two options:Set search_path = 'teleon, public';CREATE TABLE...Or,Specify the schema in front of tge type.-- Thanks, AsheshOn Tue, 6 Aug 2019, 17:14 Tony Shelver, <tshelver@gmail.com> wrote:Schema | Name | Description
--------+-----------+----------------------------------------------
| geography | postgis type: Ellipsoidal spatial data type.(1 row)On Tue, 6 Aug 2019 at 12:36, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:And - the following command for psql?
\dT geographyOn Tue, Aug 6, 2019 at 4:02 PM Tony Shelver <tshelver@gmail.com> wrote:On Tue, 6 Aug 2019 at 12:18, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:Can you please let me know the output of 'SHOW search_path' from the psql?Ashesh, if I run SHOW search_path in psql, there is no output. Just returns with the prompt.
On Tue, 6 Aug 2019 at 14:32, Ashesh Vashi <ashesh.vashi@enterprisedb.com> wrote:
If you're running from the sql editor, then - you have either of the two options:Set search_path = 'teleon, public';CREATE TABLE...Or,Specify the schema in front of tge type.-- Thanks, AsheshWill have a go at it. However, for those of us less pg-aware folks, it seems strange that running the script in psql works versus in pgdmin4. As far as i know, this worked on a prior pgAdmin4 release when I was running on Windows 10, not Ubuntu.
Will have a go at it. However, for those of us less pg-aware folks, it seems strange that running the script in psql works versus in pgdmin4. As far as i know, this worked on a prior pgAdmin4 release when I was running on Windows 10, not Ubuntu.
It also works fine if I execute the same scripts from within pgmodeler (which is where I cut the source from originally).
I am a bit wary about setting the path, I have multiple schemas.
Not a biggie for me at the moment, it was just a quick fix to test things out prior to doing a full pgmodeler DB rebuild and reload.