PostgreSQL procedure to insert a sequence of random numbers in a column

Question

I need to insert k random numbers (in the range 1:n) concatenated by | into a column of a table 'X', where n is the number of rows in table 'Y', in a PostgreSQL procedure.

To find the number of rows in the table 'Y'

select count(*) into n from Y

This will generate k random numbers in the range 1:n

SELECT num FROM GENERATE_SERIES (1, n) AS s(num) ORDER BY RANDOM() LIMIT k;

How do I concatenate the k integers with | and insert them into 'X'?

You claim that "n" is the number of columns in table y - but select count(*) into n from Y returns the number of rows in that table. Two very different things. Also: storing delimited values in a column is almost always a really bad idea. A properly normalized model is much easier to maintain and to work with — user330315
– user330315, Commented Oct 17, 2020 at 12:31
@Bergi with string_agg I need to pass the output of the query as the first argument, would I have to use a temporary table to hold the results of the query for generating the k random numbers or is there any other optimal way? — S_S
– S_S, Commented Oct 17, 2020 at 12:32
@a_horse_with_no_name Thanks for pointing that out, my bad, I corrected it to the number of rows. My use case is that I need to assign a random set of rows from multiple tables to a user for a task and need to maintain the assignments in a table. — S_S
– S_S, Commented Oct 17, 2020 at 12:35
Still: you shouldn't store delimited values in a single column. — user330315
– user330315, Commented Oct 17, 2020 at 12:59

GMB · Accepted Answer · 2020-10-17 12:30:53Z

1

You can use insert ... select and string aggregation function string_agg():

insert into y (x)
select string_agg(num::text, '|')
from (select num from generate_series (1, n) as s(num) order by random() limit k) s

Note that you need n to be equal to or greater than k for this technique to make sense.

answered Oct 17, 2020 at 12:30

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL procedure to insert a sequence of random numbers in a column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related