PG经常遇到的SQL错误

1. ERROR: operator does not exist: character varying = integer

Caused by: com.sie.snest.engine.exception.SnestException: (补充异常)SQL：SELECT "id" FROM "rbac_organization" WHERE "status" = 1 AND code_path LIKE CONCAT((SELECT o.code_path FROM "rbac_organization" o WHERE o."id"='04mebs02ragu9'),'/%') 
 入参：[]
	at com.sie.snest.engine.db.relationdb.RelationDBAccessor.doExecute(RelationDBAccessor.java:680)
	... 137 common frames omitted
2025-02-18 11:56:49.402 [http-nio-8060-exec-1] ERROR c.s.snest.engine.db.relationdb.RelationDBAccessor -数据源:main,执行SQL失败:ERROR: operator does not exist: character varying = integer
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 53 
异常SQL:SELECT "id" FROM "rbac_organization" WHERE "status" = 1 AND code_path LIKE CONCAT(( SELECT o.code_path FROM "rbac_organization" o WHERE o."id" = '04mebs02ragu9' ), '/%')

PG的数据类型转换非常严格,不允许字段类型与值的类型不匹配. 这个错误是因为 SQL 查询中字段类型与比较值的类型不匹配。具体问题出在 "status" = 1 。如果 "status" 是字符串类型（character varying），需要用 '1' 而不是 1建议先确认字段类型，然后根据类型调整 SQL 查询。例如，如果 "status" 是字符串，改为 "status" = '1'.

解决方法

  1. 检查字段类型
  2. 显式类型转换
  3. 检查 SQL 查询

仔细检查你的 SQL 查询，确保字段和参数的类型一致。例如，如果你的字段是 id（varchar 类型），不要直接写：

SELECT * FROM your_table WHERE id = 123;
而应该写：
SELECT * FROM your_table WHERE id = '123';

Table of Contents