1. Hi, I want to add a unique, non-nullable column to a table.
2. It already has data. I would therefore like to instantly populate the new column with unique values, eg 'ABC123', 'ABC124', 'ABC125', etc.
3. The data will eventually be wiped and replaced with proper data, so i don't want to introduce a sequence just to populate the default value.

Is it possible to generate a default value for the existing rows, based on something like rownumber()? I realise the use case is ridiculous but is it possible to achieve... if so how?

...
foo text not null unique default 'ABC'||rownumber()' -- or something similar?
...

CodePudding user response：

can be applied generate_series?

select 'ABC' || generate_series(123,130)::text;

 ABC123
 ABC124
 ABC125
 ABC126
 ABC127
 ABC128
 ABC129
 ABC130

Variant 2 add column UNIQUE and not null

begin;
alter table test_table add column foo text not null default 'ABC';

with s as (select id,(row_number() over(order by id))::text t from test_table) update test_table set foo=foo || s.t from s where test_table.id=s.id;

alter table test_table add CONSTRAINT unique_foo1 UNIQUE(foo);

commit;

results

select * from test_table;
id | foo
---- ------
  1 | ABC1
  2 | ABC2
  3 | ABC3
  4 | ABC4
  5 | ABC5
  6 | ABC6