CODEWITHSUNDEEP
sqlstringpostgresqlrandomsql-insert
S_S
S_S

Reputation: 1402

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

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'?

Upvotes: 0

Views: 1058

Answers (1)

GMB
GMB

Reputation: 222482

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.

Upvotes: 1

Related Questions