Unable to make generated column in postgresql for Json data

1.7k Views Asked by At

I'm trying out generated column with postgres-12. I need to create a table with generated column with JSON data. I'm going to receive "name" field as key there . However, while doing so - I got below error:

   postgres=# create table json_tab2 (data jsonb ,
   postgres(#  "json_tab2.pname" text generated always as (data ->> "name"  ) stored
   postgres(#  );
   ERROR:  column "name" does not exist
   LINE 2: ...on_tab2.pname" text generated always as (data ->> "name"  ) ...

After this: I tried to alter existing table- because that has value into json data for generated column - so it should be able to identify "name" now. This time I ran below:

  postgres=# alter table json_tab add column Pname text  generated always as (data ->> "name") stored 
   ;
   ERROR:  column "name" does not exist

However, "name" has value here:

                         data
  -------------------------------------------------
   {"age": 31, "city": "New York", "name": "John"}

I'm unable to understand - what I'm doing wrong here

1

There are 1 best solutions below

0
On BEST ANSWER

The righthand side of the ->> operator should be a value. In this case, since it's a string, you need to surround it with single quotes ('):

create table json_tab2 (
    data jsonb,
    pname text generated always as (data ->> 'name') stored
    -- Here ---------------------------------^----^
);