PostgreSQL查询报错：numeric类型输入语法无效，值为'distance'

错误原因 #

1. 你在WHERE子句中用单引号包裹了distance，导致PostgreSQL将其识别为字符串常量，而非SELECT中定义的计算字段别名。字符串无法直接与数值50比较，触发类型转换错误。
2. 即使去掉单引号，PostgreSQL的查询执行顺序是先处理WHERE子句，再处理SELECT子句，此时SELECT中定义的distance别名还未生效，同样会提示字段不存在的错误。

解决方法 #

方法一：在WHERE子句中重复计算表达式

直接将SELECT中的distance计算逻辑复制到WHERE子句中，简单直接：

SELECT premises.id, (
  6371
    *acos(cos(radians(13.75))
    *cos(radians(latitude))
    *cos(radians(longitude)
    -radians(100.5))
    +sin(radians(13.75))
    *sin(radians(latitude)))
  )::NUMERIC AS distance
FROM premises
WHERE (
  6371
    *acos(cos(radians(13.75))
    *cos(radians(latitude))
    *cos(radians(longitude)
    -radians(100.5))
    +sin(radians(13.75))
    *sin(radians(latitude)))
  )::NUMERIC < 50

方法二：使用CTE（公共表表达式）复用计算结果

先通过CTE计算出所有记录的distance，再对结果进行筛选，代码更清晰：

WITH premises_with_distance AS (
  SELECT premises.id, (
    6371
      *acos(cos(radians(13.75))
      *cos(radians(latitude))
      *cos(radians(longitude)
      -radians(100.5))
      +sin(radians(13.75))
      *sin(radians(latitude)))
    )::NUMERIC AS distance
  FROM premises
)
SELECT id, distance
FROM premises_with_distance
WHERE distance < 50

方法三：使用子查询筛选

将计算逻辑放在子查询中，外层查询直接使用别名筛选：

SELECT id, distance
FROM (
  SELECT premises.id, (
    6371
      *acos(cos(radians(13.75))
      *cos(radians(latitude))
      *cos(radians(longitude)
      -radians(100.5))
      +sin(radians(13.75))
      *sin(radians(latitude)))
    )::NUMERIC AS distance
  FROM premises
) AS sub_query
WHERE distance < 50

方法四：使用LATERAL JOIN计算一次结果

通过LATERAL JOIN将distance计算逻辑封装，避免重复代码的同时保证性能：

SELECT p.id, d.distance
FROM premises p
CROSS JOIN LATERAL (
  SELECT (
    6371
      *acos(cos(radians(13.75))
      *cos(radians(p.latitude))
      *cos(radians(p.longitude)
      -radians(100.5))
      +sin(radians(13.75))
      *sin(radians(p.latitude)))
    )::NUMERIC AS distance
) d
WHERE d.distance < 50

内容的提问来源于stack exchange，提问作者Aaron Harker