Postgresql – Insert values into new columns using values from old columns without locking table

postgresql

The premise is simple – a table has a jsonb column; this json value always has three fields, so I decided to split the three fields into three separate columns on the same table.
Since the table is really large (in the ~100m range), I need a way to insert these json values into their own respective columns without locking the table.
Is there a way to achieve this?

Best Answer

You cannot avoid locking. Keep your transactions short and the number of rows locked reasonable by performing the update in batches, e.g. by primary key range. That reduces the danger of deadlocks.

Related Solutions

Postgresql – Update new column with value from old columns for all the rows

The solution is very simple. If you do

UPDATE exchange
SET emp_id = {some expression here on the params column}; -- no WHERE clause!

it will do exactly what you want: pick the params from the row to be updated, calculate the expression and update emp_id with its value. As there is no WHERE clause, it will go through the whole table. You don't need the ID of the row or anything else.

Postgresql – How to insert values into a table with default values from a select query in PostgreSQL

If you add all ColumnsNames (exclude the 'id' Column) after the Tablename, there will be Insert the serial automaticly like:

INSERT INTO animals(nm,typ,tvi,tvf) select nm,typ,tvi,tvf from json_po.....

You can also add a DEFAULT Value in your Column, to set a Default Value if the column is not in the Insert Column-list.

Best Answer

Related Solutions

Postgresql – Update new column with value from old columns for all the rows

Postgresql – How to insert values into a table with default values from a select query in PostgreSQL

Related Question