SQL Pick random value from enum type

Question

I'm running in postgresql and i want to choose a random value from a simple enum type. I could not find a way online tho.

This is a type example:

create type letter as enum('A','B','C','D');

now i just want to randomly choose one of the letters. thanks!

Answer 1

There's a few ways of going about this. I would use enum_range to dump the enum values and then unnest to split them into their own rows, then just randomly grab one of the records:

SELECT myletter FROM ( SELECT unnest(enum_range(NULL::letter)) as myletter ) sub ORDER BY random() LIMIT 1;

I would imagine you could probably do this in a single select without the nesting using some different functions, but this is pretty clear, logically, for the next person that has to decipher what you were doing.

---

From @pozs in the comments here, apparently the subselect is superfluous since Postgres supports sticking the unnest() directly in the FROM clause:

SELECT myletter FROM unnest(enum_range(NULL::letter)) myletter ORDER BY random() LIMIT 1;