CODEWITHSUNDEEP
sqlpostgresql
Andrew Holway
Andrew Holway

Reputation: 351

Generating unique random values along with a series - creating test data for postgresql

I would like to generate a unique random() number for each row to be inserted into this table. Can anyone show me how I can generate the number and assign it to the dis field?

Thanks very much!

DROP TABLE IF EXISTS lat_longs;

CREATE TABLE lat_longs (
  id              SERIAL PRIMARY KEY,
  pos             REAL NOT NULL
  dis             REAL NOT NULL
);

insert into lat_longs(pos)
select g.position
from generate_series(0, 100, 0.001) as g(position);

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use random():

insert into lat_longs (pos, dis)
    select g.position, random()
    from generate_series(0, 100, 0.001) as g(position);

This produces a number between 0 and 1. If you want a number in a particular range, use arithmetic. For instance, between -180 and 180:

random() * 360 - 180

I should also say that random() does not guarantee that there are no duplicates. But it is very, very, very unlikely to occur.

Upvotes: 2

Related Questions