sql - Postgres 选择执行顺序不正确

Question

以下查询在 Postgres 9.4.5 中不起作用。

SELECT * FROM (
   SELECT M.NAME, M.VALUE AS V
     FROM METRICS AS M, METRICATTRIBUTES AS A
    WHERE M.NAME=A.NAME AND A.ISSTRING='FALSE'
    ) AS S1
 WHERE CAST(S1.V AS NUMERIC)<0

我收到如下错误:

invalid input syntax for type numeric: "astringvalue"

继续阅读以了解为什么我将查询设置得如此复杂。

METRICS 是一个指标、值对表。这些值存储为字符串，并且 VALUE 字段的某些值实际上是字符串。 METRICATTRIBUTES 表标识那些可能具有字符串值的指标名称。我根据对 METRICS 表的分析填充了 METRICATTRIBUTES 表。

为了检查，我跑了......

SELECT * FROM (
    SELECT M.NAME, M.VALUE AS V
      FROM METRICS AS M, METRICATTRIBUTES AS A
     WHERE M.NAME=A.NAME AND A.ISSTRING='FALSE'
    ) AS S1
 WHERE S1.V LIKE 'a%'

这不会返回任何值(如我所料)。错误似乎在执行计划中。看起来像这样(抱歉，我不得不粗指这个)

1 -> HAS JOIN 
2    HASH COND: ((M.NAME::TEXT=(A.NAME)::TEXT))
3       SEQ SCAN ON METRICS M
4       FILTER: ((VALUE)::NUMERIC<0::NUMERIC)
5   -> HASH 
6     -> Seq Scan on METRICATTRIBUTES A
7        Filter: (NOT ISSTRING)

我不是这方面的专家(只有 1 周的 Postgres 经验)，但看起来 Postgres 正在尝试在应用连接条件(第 2 行)之前应用强制转换(第 4 行)。通过这样做，它将尝试将转换应用于无效的字符串值，这正是我试图避免的!

用显式连接写这个没有任何区别。将它写成单个 select 语句是我的第一次尝试，从没想过会出现这种类型的问题。那也没有用。

有什么想法吗？

Answer 1

从你的计划中可以看出，表格METRICS正在全面扫描(Seq Scan)并根据您的条件进行过滤:CAST(S1.V AS NUMERIC)<0 —join 完全不限制范围。

显然，您有一些行在 METRICS.VALUE 中包含非数字数据。 .检查您的表中是否有这样的行:

SELECT * FROM METRICS
 WHERE NOT VALUE ~ '^([0-9].,e)*$'

请注意，很难用正则表达式捕获所有可能的组合，因此请查看此相关问题:isnumeric() with PostgreSQL

名称 VALUE对于专栏不好，因为这个词is a reserved one .

编辑:如果您完全确定，连接的表将产生想要的 VALUE -s，你可以使用 CTEs ，在 PostgreSQL 中具有优化栅栏功能:

WITH S1 AS (
    SELECT M.NAME, M.VALUE AS V
      FROM METRICS AS M
      JOIN METRICATTRIBUTES AS A USING (NAME)
     WHERE A.ISSTRING='FALSE'
)
SELECT *
  FROM S1
 WHERE CAST(S1.V AS NUMERIC)<0;