How to create a Postgresql generated column of type JSONB from columns of types JSONB, VARCHAR and DATE

Question

I have a table test_data with 4 columns as shown below. I want to create a JSONB generated column with data from these columns. Below is what I have tried but keep on getting this error:

ERROR: generation expression is not immutable SQL state: 42P17

name VARCHAR(50)	mixed_stuff JSONB	other_stuff JSONB	created TIMESTAMP	combined JSONB

CREATE TABLE test_data(name VARCHAR(50), mixed_stuff JSONB, other_stuff JSONB, created TIMESTAMP WITHOUT TIMEZONE default(now() at time zone 'utc'));

ALTER TABLE test_data
add combined JSONB GENERATED ALWAYS AS (jsonb_build_object('name',name) || "mixed_stuff" || "other_stuff" || jsonb_build_object('created',created)) STORED;

See: dba.stackexchange.com/q/264327 for sad news.

Dave S.
– Dave S.

2023-02-28 21:26:55 +00:00
Commented Feb 28, 2023 at 21:26 — Dave S.
– Dave S., Commented Feb 28, 2023 at 21:26

Ilya Kolpakov · Accepted Answer · 2022-04-06 18:59:46Z

2

Generated columns can only use immutable functions (docs) while jsonb_build_object() is only marked as stable (run \df+ jsonb_build_object in psql to check).

As a workaround one can define a wrapper function marked as immutable (but beware of consequences depending on your usage scenario):

CREATE FUNCTION jsonb_build_object_imm(VARIADIC input anyarray) RETURNS JSONB AS $$
  SELECT jsonb_build_object(VARIADIC input)
$$
LANGUAGE SQL IMMUTABLE PARALLEL SAFE;

The reason why jsonb_build_object() is only stable because to_json() is only stable as many other type conversions -- compare with json_object() which takes only strings and is marked as immutable.

edited Apr 6, 2022 at 18:59

answered Apr 6, 2022 at 18:38

Ilya Kolpakov

4264 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create a Postgresql generated column of type JSONB from columns of types JSONB, VARCHAR and DATE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related