patternsqlMajor

Set random value from set

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

randomfromsetvalue

Problem

I need to put some random values into database, but I don't want to end up with completely randomized text (like 7hfg43d3). Instead I would like to randomly pick one of values supplied by myself.

Solution

Nice idea. I suggest two minor simplifications:

('{Foo,Bar,Poo}'::text[])[ceil(random()*3)]

-
Simpler syntax using an array literal ('{Foo,Bar,Poo}'::text[])
Shortens the string for longer lists.
Additional benefit: explicit type declaration works for any type, not just for text. Your original idea happens to output text, because that's the default type for string literals.

-
Use ceil() instead of floor() + 1. Same result.

OK, theoretically, the lower bound could be 0 precisely, as hinted in your comment, since random() produces (quoting the manual here):

random value in the range 0.0 SQLfiddle

To be perfectly safe, though, you can use Postgres custom array subscripts and still avoid the extra addition:

('[0:2]={Foo,Bar,Poo}'::text[])[floor(random()*3)]

See:

Normalize array subscripts for 1-dimensional array so they start with 1

Why does PostgreSQL allow querying for array[0] even though it uses 1-based arrays?

Better yet, use trunc(), that's a bit faster.

('[0:2]={Foo,Bar,Poo}'::text[])[trunc(random()*3)]

Code Snippets

('{Foo,Bar,Poo}'::text[])[ceil(random()*3)]

SELECT count(*)
FROM   generate_series(1,1000000)
WHERE  ceil(random())::int = 0;

('[0:2]={Foo,Bar,Poo}'::text[])[floor(random()*3)]

('[0:2]={Foo,Bar,Poo}'::text[])[trunc(random()*3)]

Context

StackExchange Database Administrators Q#55363, answer score: 34

Revisions (0)

No revisions yet.