NutzCN Logo
问答 postgresql使用string_to_array && array[] 报operator does not exist: text[] && character varying[]错误
发布于 1402天前 作者 qq_fa416ace 3390 次浏览 复制 上一个帖子 下一个帖子
标签:

这是我的代码:
Sql sql = Sqls.create("SELECT * FROM t_enterprise_basic WHERE 1=1 ");
if (StringUtils.isNotBlank(townCodes)) {
List towns = new ArrayList<>();
Arrays.stream(townCodes.split(",")).map(TownEnum::getValue4ByValue)
.forEach(t -> towns.addAll(Arrays.asList(t.split(","))));
sql.appendSourceSql(" AND town IN (@towns) ");
sql.setParam("towns", towns);
}
if (StringUtils.isNotBlank(enterpriseMark)) {
sql.appendSourceSql(" AND string_to_array(enterprise_mark,',') && ARRAY[@enterpriseLabels]");
sql.setParam("enterpriseLabels", enterpriseMark.split(","));
}
if (StringUtils.isNotBlank(industryTypeName)) {
sql.appendSourceSql(" AND string_to_array(industry_type_name,',') && ARRAY[@industryTypeNames]");
sql.setParam("industryTypeNames", industryTypeName.split(","));
}
sql.setCallback(Sqls.callback.entities());
sql.setEntity(dao.getEntity(TaskEntity.class));
dao.execute(sql);

这是我的日志:
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: text[] && character varying[]
建议:No operator matches the given name and argument type(s). You might need to add explicit type casts.
位置:140
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:106)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3240)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:3237)
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:181)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227)
at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:275)
at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:59)
... 64 more

日志里打印出来的sql在数据库是可以正常执行的
SELECT
*
FROM
t_enterprise_basic
WHERE
1 = 1
AND town IN ( '伦敦' )
AND string_to_array( enterprise_mark, ',' ) && ARRAY [ ‘互联网’, '工厂' ]

2 回复

可能这种sql不支持占位符吧

根据日志,string_to_array被识别为text[],array[]被识别为character varying[],就报错了
但是sql是正常的
目前改成都用string_to_array暂时避过这个问题

添加回复
请先登陆
回到顶部