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 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.
>Solution :
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()
;