CODEWITHSUNDEEP
sqlpostgresql
RKR
RKR

Reputation: 687

How to update a table in postgresql

I have a table in the below format:

time                  year   month   day   hour   area   state1    state3 
2015-04-01 00:00:00   2015     4      1      0      1     100        300
2015-04-01 00:00:00   2015     4      1      0      2     300        300
2015-04-01 00:00:00   2015     4      1      0      3     500        600
2015-04-01 01:00:00   2015     4      1      0      1     600        900
2015-04-01 01:00:00   2015     4      1      0      2     100        300
2015-04-01 01:00:00   2015     4      1      0      3     100        600 
2015-04-01 02:00:00   2015     4      1      0      2     900        300
2015-04-01 02:00:00   2015     4      1      0      3     300        900
2015-04-01 02:00:00   2015     4      1      0      1     100        300
.......................
...........
........
2015-04-30 23:00:00   2015     4      30      23    3     100        300

The problem is after 2015-04-10 00:00:00 all of my records have 0 in state1 and state3.So I wanted to update those by a random number between 150 to 300.

I have a function which returns the random number .

SELECT random_between(1,100)
FROM generate_series(1,5);

How can I update the state1 and state3 after 2015-04-10 00:00:00 by using my function.Any help is appreciated.

Upvotes: 0

Views: 46

Answers (1)

Colin Beckingham
Colin Beckingham

Reputation: 525

Consider as an example the following session. It is a very simple example of what you are trying to do, perhaps it can be adapted to your situation. I have used a direct cast and a function:

> createdb test
> psql -d test
psql (9.4.9)
Type "help" for help.

test=# create table mytest(id integer, cola integer,colb integer);
CREATE TABLE
test=# select * from mytest;
 id | cola | colb 
----+------+------
(0 rows)

test=# insert into mytest values(1,0,0);
INSERT 0 1
test=# insert into mytest values(2,0,0);
INSERT 0 1
test=# insert into mytest values(3,0,0);
INSERT 0 1
test=# insert into mytest values(4,0,0);
INSERT 0 1
test=# select * from mytest;
 id | cola | colb 
----+------+------
  1 |    0 |    0
  2 |    0 |    0
  3 |    0 |    0
  4 |    0 |    0
(4 rows)

test=# update mytest set cola = cast(((random()*150)+150) as Integer) where id >2;
UPDATE 2
test=# select * from mytest;
 id | cola | colb 
----+------+------
  1 |    0 |    0
  2 |    0 |    0
  3 |  178 |    0
  4 |  198 |    0
(4 rows)

test=# create function myrand()
test-# returns integer as $i$
test$# declare i integer;
test$# begin
test$# return cast(((random()*150)+150) as Integer);
test$# end; 
test$# $i$ language plpgsql;
CREATE FUNCTION

test=# update mytest set colb = myrand() where id >2;
UPDATE 2
test=# select * from mytest;
 id | cola | colb 
----+------+------
  1 |    0 |    0
  2 |    0 |    0
  3 |  178 |  201
  4 |  198 |  291
(4 rows)

Upvotes: 2

Related Questions