Query that generates random unique numbers within a specific range for columns within the same row-CodePudding

I am trying to make it so any given row inserted into my table will have a random assortment of numbers within a specific range, let's say 1-5 for 5 columns that take in numbers...but if a number has already been inserted into a column, only the remaining numbers can be used for the rest.

What do I need to do to accomplish this? Here's an example of what I've done so far to populate 10 rows. (name column is fine as is, but thought I'd include it just in case that factors into an answer at all).

INSERT INTO tablename
("name", "col1", "col2", "col3", "col4", "col5")
SELECT substr(md5(random()::text), 0, 15),  (random() * 4   1), (random() * 4   1), 
(random() * 4   1), (random() * 4   1), (random() * 4   1)
FROM generate_series(1, 10)

This query in its current state repeats the same lines

It isn't a problem to me if that exact number combination across the columns repeats in a future row, so DISTINCT isn't what I am looking for. Doesn't look like I can use EXISTS/NOT EXISTS either because the row doesn't exist yet.

Perhaps NOT EXISTS in a sub query might work? But I haven't figured out a way to do it with those yet.

Appreciate any help here, thanks.

CodePudding user response：

So the following solution generates numbers from 1 to 5 using generate_series and then shuffles them into an array. Then the array is used to populate the table:

WITH generator AS (
   SELECT array_agg(gen.i ORDER BY random()) AS numbers
     FROM generate_series(1, 5) gen(i) -- range of numbers
    CROSS
     JOIN generate_series(1, 5) rows(i) -- number of rows
    GROUP BY rows.i
)
INSERT INTO tablename
(name, col1, col2, col3, col4, col5)
SELECT substr(md5(random()::text), 0, 15) AS name
     , gen.numbers[1]
     , gen.numbers[2]
     , gen.numbers[3]
     , gen.numbers[4]
     , gen.numbers[5]
  FROM generator gen

Here's a working example on dbfiddle