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

Question

The full error message is:

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

The query I'm using is:

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;

And the shared.sample function I'm using is:

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";

I'm pretty new to this stuff, so any help would be appreciated.

Answer 1

Not a problem of the function. Like the error messages says: The string '1e+06' cannot be cast to integer.

Obviously, the columns n_agents in your table results_20161109.initial_agent_count_by_tract_res_11 is not an integer column. Probably type text or varchar? (That info would help in your question.)

Either way, the assignment cast does not work for the target type integer. But it does for numeric:

Does not work:

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

Works:

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

If my assumptions hold, you can use this as stepping stone.
Replace b.n_agents in your query with b.n_agents::numeric::int.

It's your responsibility that numbers stay in integer range, or you get the next exception.

---

If that did not nail it, you need to look into function overloading:

• Is there a way to disable function overloading in Postgres

And function type resolution:

• PostgreSQL function call

The schema search path is relevant in many related cases, but you did schema-qualify all objects, so we can rule that out.

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

Your query generally looks good. I had a look and only found minor improvements:

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;