GBase8c 使用postgis报错：type “geometry“ does not exist

报错情况：

t_gis=# create table cities (
    id serial primary key,
    name varchar(100) not null,
    population integer,
    geom geometry(point, 4326) 
);

NOTICE:  CREATE TABLE will create implicit sequence "cities_id_seq" for serial column "cities.id"
ERROR:  type "geometry" does not exist
LINE 5:     geom geometry(point, 4326)

排查思路：

1、检查postgis 是否可用

t_gis=# select * from pg_available_extensions where name = 'postgis';
  name   | default_version | installed_version |                          comment
---------+-----------------+-------------------+------------------------------------------------------------
 postgis | 3.2.1           | 3.2.1             | PostGIS geometry and geography spatial types and functions
(1 row)

2、检查postgis 是否创建

t_gis=# select * from pg_extension where extname = 'postgis';
 extname | extowner | extnamespace | extrelocatable | extversion | extconfig |           extcondition
---------+----------+--------------+----------------+------------+-----------+-----------------------------------
 postgis |       10 |       114616 | f              | 3.2.1      | {123012}  | {"WHERE NOT (                    +
         |          |              |                |            |           | srid BETWEEN 2000 AND 2217       +
         

3、检查postgis 在哪个模式创建

t_gis=# SELECT e.extname AS extension_name, n.nspname AS namespace_name
FROM pg_extension e
JOIN pg_namespace n ON e.extnamespace = n.oid
where e.extname = 'postgis';

 extension_name | namespace_name
----------------+----------------
 postgis        | sdx
(1 row)

4、切换对应模式，用\dT命令 检查查看数据类型

t_gis=# set search_path to sdx;
SET
t_gis=# \dT
          List of data types
 Schema |     Name      | Description
--------+---------------+-------------
 sdx    | box2d         |
 sdx    | box2df        |
 sdx    | box3d         |
 sdx    | geography     |
 sdx    | geometry      |
 sdx    | geometry_dump |
 sdx    | gidx          |
 sdx    | spheroid      |
 sdx    | valid_detail  |
(9 rows)

t_gis=# -- SQL 查询
SELECT n.nspname as "Schema",
  pg_catalog.format_type(t.oid, NULL) AS "Name",
  pg_catalog.obj_description(t.oid, 'pg_type') as "Description"
FROM pg_catalog.pg_type t
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid))
  AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'db4ai'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_type_is_visible(t.oid)
ORDER BY 1, 2;

 Schema |     Name      | Description
--------+---------------+-------------
 sdx    | box2d         |
 sdx    | box2df        |
 sdx    | box3d         |
 sdx    | geography     |
 sdx    | geometry      |
 sdx    | geometry_dump |
 sdx    | gidx          |
 sdx    | spheroid      |
 sdx    | valid_detail  |
(9 rows)

5、查询到数据类型在sdx模式，重新执行建表语句

t_gis=# create table cities (
    id serial primary key,
    name varchar(100) not null,
    population integer,
    geom sdx.geometry(point, 4326)
);
NOTICE:  CREATE TABLE will create implicit sequence "cities_id_seq" for serial column "cities.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "cities_pkey" for table "cities"
CREATE TABLE

t_gis=# \d+ cities
                                                        Table "public.cities"
   Column   |           Type           |                      Modifiers                      | Storage  | Stats target | Description
------------+--------------------------+-----------------------------------------------------+----------+--------------+-------------
 id         | integer                  | not null default nextval('cities_id_seq'::regclass) | plain    |              |
 name       | character varying(100)   | not null                                            | extended |              |
 population | integer                  |                                                     | plain    |              |
 geom       | sdx.geometry(Point,4326) |                                                     | main     |              |
Indexes:
    "cities_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no