数据迁移

INSERT INTO data_water_drinking_water_sensor_weekly (timestamp, report_time, device_sn, data )
select
       timestamp,
       timestamp as "report_time",
       'water_' || ws.sensor_id as "device_sn",
       (('{"grade":' || ws."grade" || ','
         || '"pH":' || ws."pH" || ','
         || '"DO":' || ws."DO" || ','
         || '"CODmn":' || ws."CODmn" || ','
         || '"NH3_N":' || ws."NH3_N" || ','
         || '"temp":' || ws."temp" || ','
         || '"conductivity":' || ws.conductivity || ','
         || '"grade_pH":' || ws."grade_pH" || ','
         || '"grade_DO":' || ws."grade_DO" || ','
         || '"grade_CODmn":' || ws."grade_CODmn" || ','
         || '"grade_NH3_N":' || ws."grade_NH3_N" || ','
         || '"sampling":' || ws."water_sampling" || ','
--          || ' "grade_temp":' || ws."grade_temp" || ','
--          || '"grade_conductivity":' || ws."grade_conductivity" || ','
         || '"primary_pollutant":'
         || (CASE WHEN ws."main_pollutant" is null THEN 'null' ELSE '"' || ws."main_pollutant" || '"' end)
         || '}')::jsonb) as data
from water_sensor_record_sensor_weekly as "ws" where ws.sensor_id in (select ws1.id from water_sensor ws1 where ws1.type = 'DrinkingWaterSection');


truncate TABLE  data_water_drinking_water_sensor_weekly;