PostGIS查询用NOT ILIKE时出现匿名复合类型未实现错误求助

问题详情 #

我有如下PostGIS查询：

with 
f as ( SELECT
    ST_AsGeoJSON(p.geompos)::json As geometry, 
    row_to_json((p.id_client, p.design_site, p.adresse)) As properties
    FROM sites p
    where p.id_client ilike '1%'

),  
fc as ( SELECT 
       'FeatureCollection' As type, 
       array_to_json(array_agg(f)) As features 
       FROM f 
      ) 
SELECT row_to_json(fc) FROM fc

该查询运行正常，但将条件改为：

where p.id_client not ilike '1%'

时，出现错误：

ERROR:  input of anonymous composite types is not implemented 
SQL state: 0A000

怀疑是部分几何数据导致问题，但数据量超过22000行，无法定位具体问题行。以下是sites表的DDL：

CREATE TABLE IF NOT EXISTS public.sites
(
    idsite serial,
    id_client character varying(8) COLLATE pg_catalog."default",
    design_site character varying(1024) COLLATE pg_catalog."default",
    adresse character varying(1024) COLLATE pg_catalog."default",
    geompos geometry,
    CONSTRAINT pk_sites PRIMARY KEY (idsite),
    CONSTRAINT fk_sites_associati_ref_clie FOREIGN KEY (id_client)
        REFERENCES public.ref_client (id_client) MATCH SIMPLE
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
)

问题原因 #

这个错误和几何数据无关，核心问题出在匿名复合类型的JSON转换逻辑上：你用row_to_json((p.id_client, p.design_site, p.adresse))创建了一个没有字段名的匿名元组，当结果集中存在NULL值或特定数据类型时，PostgreSQL的row_to_json无法处理这种匿名复合类型的转换，而id_client ilike '1%'的结果集刚好没触发这个问题，not ilike '1%'的结果集里存在这类触发条件的数据。

解决方法 #

两种可行的修复方式，都是避免使用匿名元组，改用显式的JSON构造方式：

方法1：用子查询构造带字段名的行 #

with 
f as ( SELECT
    ST_AsGeoJSON(p.geompos)::json As geometry, 
    row_to_json(
        (SELECT x FROM (SELECT p.id_client, p.design_site, p.adresse) x)
    ) As properties
    FROM sites p
    where p.id_client not ilike '1%'

),  
fc as ( SELECT 
       'FeatureCollection' As type, 
       array_to_json(array_agg(f)) As features 
       FROM f 
      ) 
SELECT row_to_json(fc) FROM fc

方法2：用json_build_object直接构造JSON对象（更直观） #

with 
f as ( SELECT
    ST_AsGeoJSON(p.geompos)::json As geometry, 
    json_build_object(
        'id_client', p.id_client,
        'design_site', p.design_site,
        'adresse', p.adresse
    ) As properties
    FROM sites p
    where p.id_client not ilike '1%'

),  
fc as ( SELECT 
       'FeatureCollection' As type, 
       array_to_json(array_agg(f)) As features 
       FROM f 
      ) 
SELECT row_to_json(fc) FROM fc

可选：验证几何数据（非必须） #

如果还是担心几何数据有问题，可以执行以下查询排查：

-- 检查无效的几何对象
SELECT idsite, id_client, ST_IsValidReason(geompos)
FROM sites
WHERE id_client not ilike '1%' AND NOT ST_IsValid(geompos);

-- 检查空几何对象
SELECT idsite, id_client
FROM sites
WHERE id_client not ilike '1%' AND geompos IS NULL;

内容的提问来源于stack exchange，提问作者عبد القادر كعوان