CODEWITHSUNDEEP
postgresql
nimgwfc
nimgwfc

Reputation: 1509

Add column and set default to value in other field

I have a table in my database that needs to be restructured. It currently looks like this:

id  |              attrs  
----+--------------------------------------------------------------------------------------
  1 | {"type": "test_type", "m_attrs": {"left_values": "Jack", "right_values": "John"}}

However I am wanting to alter this table and split the attrs column into separate columns for type, left_values and right_values:

alter table t_name add column if not exists type text;
alter table t_name add column if not exists left_values text;
alter table t_name add column if not exists right_values text;

What is the best way to make these columns not null and have their default value set to the value in the existing attrs column, ie: attrs->m_attrs->>'left_value'

The table is very large in terms of rows so I'm wary of performance issues too.

Upvotes: 1

Views: 581

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247865

From PostgreSQL v12 on, it would be best to use generated columns:

ALTER TABLE t_name
   ADD "type" text GENERATED ALWAYS AS (attrs->>'type') STORED,
   ADD left_values text GENERATED ALWAYS AS (attrs->'m_attrs'->>'left_values') STORED,
   ADD right_values text GENERATED ALWAYS AS (attrs->'m_attrs'->>'right_values') STORED;

This will require a table rewrite, so it will take some time and space, but there is no way to avoid a table rewrite with an operation like that anyway. You can reduce the impact by doing it in a single ALTER TABLE as above.

For older database versions, you have to do it in steps:

  • start a transaction so that the next steps blocks access to the table
  • add (nullable) columns
  • fill the columns with a single UPDATE
  • turn the columns NOT NULL if desired
  • create a trigger that fills the new columns automatically
  • commit the transaction

Apart from providing atomicity, the transaction makes sure that no new rows are added before the trigger is in place.

After that, it might be good to VACUUM (FILL) the table (still more down time) to get rid of the bloat from the UPDATE.

Upvotes: 2

Related Questions