I have this column on my user table hat_color in my PostgreSQL database.

I would like to set a randomly chosen color value as the default value if no value is provided when creating the column.

So the default value should be one of ['red', 'blue', 'green']

I was thinking something like

['red','blue','green'][floor(random() * (h-l 1)   l)::int]::text

but that does does not work. Is this possible to do and if so how?

CodePudding user response：

The proper syntax:

(array['red','blue','green'])[floor(random()* 3  1)::int]
-- or
('{red,blue,green}'::text[])[floor(random()* 3  1)::int]

Test it in db<>fiddle.