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
– Laurenz Albe, Commented Feb 15, 2022 at 21:36
@LaurenzAlbe, any idea for the solution? I tried so many possible combinations — M4rk
– M4rk, Commented Feb 16, 2022 at 0: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
– Laurenz Albe, Commented Feb 16, 2022 at 6: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
– M4rk, Commented Apr 1, 2022 at 1:56

JelteF · Accepted Answer · 2022-04-25 13:12:28Z

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

Collectives™ on Stack Overflow

Postgres Citus, immutable date conversion

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related