How convert data in database?

dukati · #1 (**permalink**) 06-30-09, 05:27

Hi alll
i want to convert raw data using math function to voltage value.That function giving as x*625/1024. x =raw data. That converting process happen in PostgreSQL database.But how to do it?TQ all.

loquin · #2 (**permalink**) 06-30-09, 17:37

Create a new table, identical to the original table, except with voltage field.

Insert data into the new table, using a subselect in the insert statement. Something along the lines of:

Code:

Insert into NewTable (Field1, Field2, ... Voltage)
Select OldTable.Field1, OldTable.Field2, ... OldTable.RawData * 625/1024 as Voltage

dukati · #3 (**permalink**) 07-01-09, 06:39

tq loquin,

but raw value still not change to voltage value after refreshed.i give the code below.what wrong?

Code:

 DELETE FROM mts400_results_l
  WHERE mts400_results_l.nodeid = new.nodeid;
 INSERT INTO mts400_results_l(result_time, epoch, nodeid, parent, voltage, humid, humtemp, prtemp, press, taosch0, taosch1, accel_x, accel_y) 
  VALUES (new.result_time, new.epoch, new.nodeid, new.parent, new.voltage, new.humid, new.humtemp, new.prtemp, new.press, new.taosch0, new.taosch1, new.accel_x, new.accel_y);
  SELECT mts400_results_l.voltage* 625/1024 AS voltage

Code:

SELECT mts400_results_l.voltage* 625/1024 AS voltage

that customised code not work?compilling not have any problem but nothing change in data.still same.tq very much for who can helping me...

loquin · #4 (**permalink**) 07-01-09, 19:19

Create a new table with the same fields as the existing table.

Insert into the NEW table, using a sub-select from the original table as the source for the select.

You can also use the Create Table As syntax to create a table which contains data from a select...

Code:

CREATE TABLE mts400_results_NEW AS
  Select result_time, epoch, nodeid, parent, voltage * 625/1024 as voltage, humid, humtemp, prtemp, press, taosch0, taosch1, accel_x, accel_y
  From mts400_results_1

From a safety viewpoint, I would not try to update the data 'in place.' Create the new table, copy the old data into the new table, processing the voltage as you do so.

Then, after you verify that the voltage data is correct, use the new table as a data source for updating the existing table

dukati · #5 (**permalink**) 07-03-09, 06:08

Code:

CREATE RULE "cache_mts400_results" AS ON INSERT TO "public"."mts400_results"

always get error with this code.what wrong?