PostgreSQL - Loop Over Rows to Fill NULL Values

Question

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.

Yes, this is exactly what you have to do - but also must add a fallback in case the formula produces a collision - duplicate "unique" IDs. — IVO GELOV
– IVO GELOV, Commented Mar 29, 2022 at 6:45
@IVOGELOV Correct, I already have that logic handled in my server-side code but unfortunately still need to figure out how to do it SQL wise. — Mohammed Mortaga
– Mohammed Mortaga, Commented Mar 29, 2022 at 6:46

user330315 · Accepted Answer · 2022-03-29 06:49:27Z

2

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

answered Mar 29, 2022 at 6:49

user330315

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

2 Comments

Mohammed Mortaga Over a year ago

This does the trick perfectly. I do wanna bring up what @IVOGELOV commented though, is there a proper way to modify this so that collisions aren't a problem?

user330315 Over a year ago

You should use a "generator" that is guaranteed to generate unique IDs (which yours is not)

Collectives™ on Stack Overflow

PostgreSQL - Loop Over Rows to Fill NULL Values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related