Inserting random data in PostgreSQL but with choices

Question

So I'm inserting a data for 3k columns but I want them to have random set of inserts to be inserted. For example:

('ATMOUTOFSERVICE','ATMBEINGSERVICE','NETWORKCONNECTIONLOST','BLACKSCREEN')

Is there any possible way that i can insert them in my columns randomly but just within those choices?

You can't have a table with 3000 columns. Did you mean 3000 rows? — user330315
– user330315, Commented Apr 3, 2020 at 8:40
Must it be a pure Postgres solution? If not, you could write a short Python, Java etc. program creating and executing INSERT statements. — user3992979
– user3992979, Commented Apr 3, 2020 at 8:42

user330315 · Accepted Answer · 2020-04-03 08:42:00Z

3

You can use something like this:

insert into target_table (some_column)
select case (random() * 3 + 1)::int
          when 1 then 'ATMOUTOFSERVICE'
          when 2 then 'ATMBEINGSERVICE'
          when 3 then 'NETWORKCONNECTIONLOST'
          when 'BLACKSCREEN'
       end
from generate_series(1,...);

Or use an array:

insert into target_table (some_column)
select (array['ATMOUTOFSERVICE',
              'ATMBEINGSERVICE',
              'NETWORKCONNECTIONLOST',
              'BLACKSCREEN'])[(random() * 3 + 1)::int]
from generate_series(1, ...);

answered Apr 3, 2020 at 8:42

user330315

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

3 Comments

Elijah Leis Over a year ago

can i use this on UPDATE query?

Elijah Leis Over a year ago

UPDATE dashboard.event SET event_description = ( select (array['CAM OUT OF SERVICE', 'CAM BEING SERVICE', 'NETWORK CONNECTION LOST', 'BLACKSCREEN', 'BNA DEVICE ERROR'])[(random() * 3 + 2)::int] from generate_series(1, 5)); i keep using this code but it won't update the null values

user330315 Over a year ago

Get rid of the select and generate_series:

SET event_description = (array['ATMOUTOFSERVICE',               'ATMBEINGSERVICE',               'NETWORKCONNECTIONLOST',               'BLACKSCREEN'])[(random() * 3 + 1)::int]

Collectives™ on Stack Overflow

Inserting random data in PostgreSQL but with choices

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related