postgres: temporary column default that is unique and not nullable, without relying on a sequence?

Question

Hi, I want to add a unique, non-nullable column to a table.
It already has data. I would therefore like to instantly populate the new column with unique values, eg 'ABC123', 'ABC124', 'ABC125', etc.
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?
...

"The data will eventually be wiped and replaced with proper data" - just do that before adding the uniqueness (or non-nullability) constraint — Bergi
– Bergi, Commented Nov 5, 2021 at 0:19
I have no control over what will be done with this model and when. I agree this seems like an unnecessary (or poorly-timed) stopgap. But it did make me curious as to whether it's possible or not. — yen
– yen, Commented Nov 5, 2021 at 0:33
@a_horse_with_no_name nothing; It just wasn't going to be used for anything after this hack and would just be detritus in the model (i couldn't remove it because the columns i used it to populate were dependants preventing deletion). In the end i said F it, and used a sequence. — yen
– yen, Commented Nov 5, 2021 at 13:23

arutar · Accepted Answer · 2021-11-05 08:10:31Z

1

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

edited Nov 5, 2021 at 8:10

answered Nov 5, 2021 at 1:05

arutar

1,0936 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

yen Over a year ago

getting error 'set-returning functions are not allowed in DEFAULT expressions'. (same for update expressions).

Belayer Over a year ago

You did not say anything about using a function. Without seeing the code there is nothing that can be done to help. Post your code, as text - no images.

yen Over a year ago

I'm accepting this answer even tho i used a sequence in the end because it's v helpful and does the job! @Belayer what are you talking about? I indeed did NOT say anything about using a function... because I'm not?

Collectives™ on Stack Overflow

postgres: temporary column default that is unique and not nullable, without relying on a sequence?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related