Search code examples
sqlpostgresqljsonbgenerated-columns

postgres GENERATED ALWAYS jsonb with refers to other columns inside a string

Trying to generate a jsonb column that will look like this: ["c:cluster-x", "ns:cluster-x/namespace-1"]

The cluster and namespace will be taken from other fields. I'm struggling with finding a way to concat it successfully

Also an array literal will be fine here, like this '{c:cluster-x, ns:cluster-x/namespace-1}'

I tried something like this:

ALTER TABLE my_table ADD COLUMN resources jsonb GENERATED ALWAYS AS ('["c:"' || my_table."clusterName" || ']'::jsonb) STORED;

but getting: Detail: Expected JSON value, but found "]".

postgres version 13.4

Solution

  • You are casting a single character ] as jsonb. That's why you're getting this error. Also, the : is missing (misplaced?) between key and value.

    This should work:

    ALTER TABLE my_table ADD COLUMN resources jsonb 
GENERATED ALWAYS AS (('[{"c":"' || "clusterName" || '"}]')::jsonb) STORED;

    Note: This solution will return NULL if the column clusterName is NULL.

    Demo: db<>fiddle