I have an existing table "public.trip_info" in PostgreSQL and I added a new column "zone" to it and now I wanted to insert random integers between 1 to 80 in the column.So I know how to generate random numbers 1 to 80 in PostgreSQL.
SELECT floor(random() * 80 + 1)::int;
How to insert this to the column for all the rows. I am new to SQL Script.
How about this?
update public.trip_info
set zone = floor(random() * 80 + 1)::int;
It's been awhile since I had written in postgres. However, idea is pretty much the same for all SQL based databases.
In order to insert a random value into a new column, you must do an UPDATE against the table.
Below is an example using MySQL:
-- Initial create & insert
mysql> create table t1(
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.20 sec)
mysql> insert into t1 values('blah blah blah'), ('aaa');
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----------------+
| name |
+----------------+
| blah blah blah |
| aaa |
+----------------+
2 rows in set (0.00 sec)
-- alter table
mysql> alter table t1 add zone int not null;
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----------------+------+
| name | zone |
+----------------+------+
| blah blah blah | 0 |
| aaa | 0 |
+----------------+------+
2 rows in set (0.00 sec)
-- update specific column
mysql> UPDATE t1 SET zone=floor(rand()*(80+1)) WHERE name='aaa';
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+----------------+------+
| name | zone |
+----------------+------+
| blah blah blah | 0 |
| aaa | 6 |
+----------------+------+
2 rows in set (0.00 sec)
-- update entire table at once
mysql> UPDATE t1 SET zone=floor(rand()*(80+1));
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from t1;
+----------------+------+
| name | zone |
+----------------+------+
| blah blah blah | 67 |
| aaa | 76 |
+----------------+------+
2 rows in set (0.00 sec)
Hope that helps
