How to add a column to the database with data from another column?

Question

PostgreSQL

There was a need to add a new item_id column and fill it with id values ​​from another column that looks like

I had an idea to write a function that would return the id value from the jsonb object for the current row and specify this function as the default value, but I don’t know how to pass the value of the item field for the current row.

CREATE FUNCTION public.get_item_id(item items)
    RETURNS bigint
    LANGUAGE 'sql'
    
AS $BODY$ 
SELECT item.item ->> "id"
FROM items
$BODY$;

ALTER FUNCTION public.get_item_id()
    OWNER TO postgres;

This code doesn't work.

Maybe there is a much easier way? Without using functions?

Answer 1

First, add a new column:

ALTER TABLE items ADD COLUMN item_id VARCHAR (16) DEFAULT NULL;

Then fill the created column with data from another column: In the example above, items is a table, and item is a jsonb column If the new BIGINT column is:
UPDATE items SET item_id = item->>'id';

ALTER TABLE items ADD COLUMN item_id_int BIGINT DEFAULT NULL;
UPDATE items SET item_id_int = (item->>'id')::bigint;