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?

The problem is the implicit cast from `timestamp with time zone` to `text` that happens because you use it as an argument to `replace`. — Laurenz Albe, Feb 15 '22 at 21:36
@LaurenzAlbe, any idea for the solution? I tried so many possible combinations — M4rk, Feb 16 '22 at 00:08
I tried to understand the query, but it makes no sense to me at all. What is that `-` supposed to do?, WHat is that attempt to concatenate the string `created_time` with a `jsonb`? — Laurenz Albe, Feb 16 '22 at 06:22
@LaurenzAlbe the goal is updating the jsonb `created_time` field. To do so, from my understanding I have to remove it and insert it again through that code. — M4rk, Apr 01 '22 at 01:56

score 1 · Answer 1 · answered Apr 25 '22 at 13:12

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

Postgres Citus, immutable date conversion

1 Answers1