PostgreSQL 错误 : invalid input syntax for integer: "1e+06"

Question

完整的错误信息是:

ERROR: invalid input syntax for integer: "1e+06"
SQL state: 22P02
Context: In PL/R function sample

我正在使用的查询是:

WITH a as
(
 SELECT a.tract_id_alias,
     array_agg(a.pgid ORDER BY a.pgid) as pgids,
     array_agg(a.sample_weight_geo ORDER BY a.pgid) as block_weights
 FROM results_20161109.block_microdata_res_joined a
 WHERE a.tract_id_alias in (66772, 66773, 66785, 66802, 66805, 66806, 66813)
 AND a.bldg_count_res > 0 
 GROUP BY a.tract_id_alias

)
SELECT NULL::INTEGER agent_id, 
     a.tract_id_alias,
     b.year,
    unnest(shared.sample(a.pgids, 
                       b.n_agents, 
                       1 * b.year, 
                       True, 
                       a.block_weights)
                       ) as pgid
FROM a
LEFT JOIN results_20161109.initial_agent_count_by_tract_res_11 b
ON a.tract_id_alias = b.tract_id_alias
ORDER BY b.year, a.tract_id_alias, pgid;

我使用的 shared.sample 函数是:

CREATE OR REPLACE FUNCTION shared.sample(ids bigint[], size integer, seed integer DEFAULT 1, with_replacement boolean DEFAULT false, probabilities numeric[] DEFAULT NULL::numeric[])
  RETURNS integer[] AS
$BODY$
    set.seed(seed)
    if (length(ids) == 1) {
        s = rep(ids,size)
    } else {
        s = sample(ids,size, with_replacement,probabilities)
    }
    return(s)
$BODY$
  LANGUAGE plr VOLATILE
  COST 100;
ALTER FUNCTION shared.sample(bigint[], integer, integer, boolean, numeric[])
  OWNER TO "server-superusers";

我对这些东西很陌生，所以任何帮助都将不胜感激。

Answer 1

不是函数的问题。正如错误消息所说:字符串 '1e+06' 无法转换为 integer。

显然，表 results_20161109.initial_agent_count_by_tract_res_11 中的列 n_agents 不是 integer 列。可能键入 text 或 varchar？ (该信息有助于解决您的问题。)

无论哪种方式，赋值转换都不适用于目标类型 integer。但它适用于 numeric:

不起作用:

SELECT '1e+06'::text::int;  -- error as in question

作品:

SELECT '1e+06'::text::numeric::int;

如果我的假设成立，您可以以此为垫脚石。
将查询中的 b.n_agents 替换为 b.n_agents::numeric::int。

您有责任让数字保持在整数范围内，否则您将遇到下一个异常。

---

如果还是不行，你需要研究一下函数重载:

• Is there a way to disable function overloading in Postgres

和函数类型解析:

• PostgreSQL function call

模式搜索路径在许多相关案例中都是相关的，但您确实对所有对象进行了模式限定，因此我们可以排除这种情况。

• How does the search_path influence identifier resolution and the "current schema"

您的查询总体上看起来不错。我看了一下，只发现了一些小的改进:

SELECT NULL::int AS agent_id  -- never omit the AS keyword for column alias
     , a.tract_id_alias
     , b.year
     , s.pgid
FROM  (
   SELECT tract_id_alias
        , array_agg(pgid)              AS pgids
        , array_agg(sample_weight_geo) AS block_weights
   FROM  (  -- use a subquery, cheaper than CTE
      SELECT tract_id_alias
           , pgid
           , sample_weight_geo
      FROM   results_20161109.block_microdata_res_joined
      WHERE  tract_id_alias IN (66772, 66773, 66785, 66802, 66805, 66806, 66813)
      AND    bldg_count_res > 0
      ORDER  BY pgid  -- sort once in a subquery. cheaper.
      ) sub
   GROUP  BY 1
   ) a
LEFT   JOIN results_20161109.initial_agent_count_by_tract_res_11 b USING (tract_id_alias)
LEFT   JOIN LATERAL
   unnest(shared.sample(a.pgids
                      , b.n_agents
                      , b.year  -- why "1 * b.year"?
                      , true
                      , a.block_weights)) s(pgid) ON true
ORDER  BY b.year, a.tract_id_alias, s.pgid;