ERROR 000210: SELECT（postgis_type_name(new_type,new_dim) IS NULL)

ERROR 000210: SELECT（postgis_type_name(new_type,new_dim) IS NULL)

开发在做postgis导入shp报错。

经分析pg库缺少postgis_type_name这样的函数。

但检查插件貌似是正常的。

对比一台正常的pg服务器，发现确实是缺少函数：

处理办法：
删除插件，再重新创建，并检查函数是否存在。
如果还有问题，则重新编译postgis插件。

lyxt=# \dx
                                    List of installed extensions
      Name      | Version |   Schema   |                        Description                         
----------------+---------+------------+------------------------------------------------------------
 pgrouting      | 2.6.3   | public     | pgRouting Extension
 plpgsql        | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis        | 3.1.3   | public     | PostGIS geometry and geography spatial types and functions
 postgis_raster | 3.1.3   | public     | PostGIS raster types and functions
 postgis_sfcgal | 3.1.3   | public     | PostGIS SFCGAL functions
(5 rows)

lyxt=# drop extension pgrouting;
DROP EXTENSION
lyxt=# drop extension postgis_sfcgal;
DROP EXTENSION
lyxt=# drop extension postgis_raster;
DROP EXTENSION
lyxt=# drop extension postgis cascade;
NOTICE:  drop cascades to column test1 of table test1
DROP EXTENSION
lyxt=# create extension postgis;
CREATE EXTENSION
lyxt=# create extension postgis_raster;
CREATE EXTENSION
lyxt=# create extension postgis_sfcgal;
CREATE EXTENSION
lyxt=# create extension pgrouting;
CREATE EXTENSION
lyxt=# \df postgis_type_name;
                                                                List of functions
 Schema  |       Name        | Result data type  |                                  Argument data types
 | Type
---------+-------------------+-------------------+---------------------------------------------------------------------------------------
-+------
 louyudb | postgis_type_name | character varying | geomname character varying, coord_dimension integer, use_new_name boolean DEFAULT true
 | func
(1 row)

lyxt=# 

复制

继续查找原因：发现是因为show search_path; //查看当前schema路径没有包括public，导致无法使用到。
执行如下设置后正常：

alter database lyxt set search_path "$user",public;

复制