Postgres Citus, immutable date conversion

Question

Trying to update some dates programmatically on Citus I always get

[0A000] ERROR: STABLE functions used in UPDATE queries cannot be called with column references

From a query like

UPDATE date_container SET json_value = json_value::jsonb - 'created_time' || CONCAT('{"created_time":"',
                                     rtrim(replace(to_timestamp(((json_value->>'created_time')::numeric/1000000))::text,' ','T'), '-05'),'"}')::jsonb

In theory all methods are immutable, but for some reasons it says that some part of it is not.

I tried also all methods below: PostgreSQL: how to convert from Unix epoch to date?

Answer 1

The CONCAT function is stable instead of immutable, this is often the case for functions that take any/anyelement as an argument.

select proname, pronamespace::regnamespace, provolatile
from pg_proc
where proname = 'concat';
 proname ? pronamespace ? provolatile
??????????????????????????????????????
 concat  ? pg_catalog   ? s

Instead you should be able to use the string concatenation operator ||, but be sure to cast all items to text, otherwise you might get the same problem with it using a anyelement version of the || operator.

So I think this query should work:

UPDATE date_container SET json_value = json_value::jsonb - 'created_time' ||
   (
      '{"created_time":"'::text
      || rtrim(replace(to_timestamp(((json_value->>'created_time')::numeric/1000000))::text,' ','T'), '-05')::text
      || '"}'::text
   )::jsonb