PostgreSQL - Loop Over Rows to Fill NULL Values-CodePudding

I have a table named players which has the following data

 ------ ------------ 
| id   | username   |
|------ ------------|
| 1    | mike93     |
| 2    | james_op   |
| 3    | will_sniff |
 ------ ------------

desired result:

 ------ ------------ ------------ 
| id   | username   | uniqueId   |
|------ ------------ ------------|
| 1    | mike93     | PvS3T5     |
| 2    | james_op   | PqWN7C     |
| 3    | will_sniff | PHtPrW     |
 ------ ------------ ------------

I need to create a new column called uniqueId. This value is different than the default serial numeric value. uniqueId is a unique, NOT NULL, 6 characters long text with the prefix "P".

In my migration, here's the code I have so far:

ALTER TABLE players ADD COLUMN uniqueId varchar(6) UNIQUE;

(loop comes here)

ALTER TABLE players ALTER COLUMN uniqueId SET NOT NULL;

and here's the SQL code I use to generate these unique IDs

SELECT CONCAT('P', string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), ''))
FROM   generate_series(1, 5);

So, in other words, I need to create the new column without the NOT NULL constraint, loop over every already existing row, fill the NULL value with a valid ID and eventually add the NOT NULL constraint.

CodePudding user response：

In theory it should be enough to run:

update players
  set unique_id = (SELECT CONCAT('P', string_agg ...))
;

However, Postgres will not re-evaluate the expression in the SELECT for every row, so this generates a unique constraint violation. One workaround is to create a function (which you might want to do anyway) that generates these fake IDs

create function generate_fake_id()
returns text
as
$$
  SELECT CONCAT('P', string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), ''))
  FROM   generate_series(1, 5)
$$
language sql
volatile;

Then you can update your table using:

update players
  set unique_id = generate_fake_id()
;

Online example