postgres: add column to table - as copy of other colum

postgres_copy_column.sql

scenario
======================

given:

table wf_test
- wf_id (PK NOT NULL)

should become:

table wf_test
- wf_id (PK NOT NULL)
- wf_root_id (FK wf_test.wf_id) NOT NULL

Example
=======

(source) rows:
wf_id = A
wf_id = B

should be come ...

(sink) rows:
wf_id = A, wf_root_id = A
wf_id = B, wf_root_id = B



what needs to be done?
======================
1. add column: wf_root_id NULL REFERENCES wf_test(wf_id)
2. foreach(row) -> set row.wf_root_id = row.wf_id
3. alter column: wf_root_id SET NOT NULL

The simple way ...

update wf_test set root_id = wf_id where root_id IS NULL;

Here we go in sql - the hard way ...
====================================

1. 

ALTER TABLE wf_test ADD COLUMN wf_root_id TEXT NULL REFERENCES wf_test(wf_id);

2. 

UPDATE wf_test sink
SET wf_root_id = src.wf_id
FROM (
  SELECT wf_id, wf_root_id 
  FROM wf_test
  ORDER BY 1
  ) src
WHERE sink.wf_id = src.wf_id AND sink.wf_root_id IS NULL AND src.wf_id IS NOT NULL;

3.

ALTER TABLE wf_test ALTER COLUMN wf_root_id SET NOT NULL;