添加jsonb类型存储列报错：context-dependent operators不允许在计算列中使用

Got it, let's break down why you're hitting that ERROR: jsonb_build_object(): context-dependent operators are not allowed in computed column error and fix it properly.

核心错误原因 #

The main issue here is incorrect usage of jsonb_build_object()—this function requires paired key-value arguments (e.g., jsonb_build_object('key_name', value)), but you're passing just d->'e' or d->'s' directly.

When you do that, PostgreSQL tries to treat the jsonb value from d->'e' as an object key, which isn't valid (object keys need to be text strings). This invalid parameter setup, combined with PostgreSQL's requirement that stored computed columns use immutable, context-independent expressions, triggers the error you're seeing.

修正后的SQL语句 #

You have two clean ways to achieve your goal, depending on your needs:

方式1：一次性构建目标JSONB对象（更简洁） #

If you just want a jsonb column c containing the e and s fields from column d, you can build the entire object in one jsonb_build_object() call:

ALTER TABLE t ADD COLUMN c jsonb AS (jsonb_build_object('e', d->'e', 's', d->'s')) STORED;

方式2：合并两个独立JSONB对象（适合扩展场景） #

If you specifically need to merge separate jsonb objects (e.g., you might add more objects later), just fix each jsonb_build_object() call to use proper key-value pairs first:

ALTER TABLE t ADD COLUMN c jsonb AS (jsonb_build_object('e', d->'e') || jsonb_build_object('s', d->'s')) STORED;

为什么这能解决问题 #

Both versions use valid, immutable expressions:

• Each jsonb_build_object() now receives a text key (like 'e') and its corresponding value (d->'e'), which is the correct syntax for the function.
• The || operator for jsonb is fully immutable, so PostgreSQL recognizes the entire expression as safe for a stored computed column.

You can test this by running the corrected statement—your column c should be created successfully, and it will automatically populate with the expected jsonb values based on column d.

内容的提问来源于stack exchange，提问作者Suriyaa Thanigaivel