1

2

3

4

5

6

7

8

9

10

11

12

13

14

DROP TABLE IF EXISTS "public"."devicedata";

CREATE TABLE "public"."devicedata" (

  "Id" varchar(200) COLLATE "pg_catalog"."default" NOT NULL,

  "DeviceId" varchar(200) COLLATE "pg_catalog"."default",

  "Timestamp" int8,

  "DataArray" float4[]

)

CREATE INDEX "timeIndex" ON "public"."devicedata" USING btree (

  "Timestamp" "pg_catalog"."int8_ops" DESC NULLS LAST,

  "DeviceId" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST

);

ALTER TABLE "public"."devicedata" ADD CONSTRAINT "devicedata_pkey" PRIMARY KEY ("Id");

1

2

3

4

5

6

7

DELETE FROM "DeviceData"

WHERE "Id"

NOT IN (

SELECT max("Id")

FROM "DeviceData_temp"

GROUP BY "DeviceId", "Timestamp"

);

1

2

3

4

5

6

7

SELECT DISTINCT "Timestamp", "DeviceId"

INTO "DeviceData_temp"

FROM "DeviceData";

-- 删除原表

DROP TABLE "DeviceData";

-- 将新表重命名

ALTER TABLE "DeviceData_temp" RENAME TO "DeviceData";

1

2

3

4

5

6

7

INSERT INTO "DeviceData_temp"

SELECT * FROM "DeviceData"

on conflict("DeviceId", "Timestamp") DO NOTHING;

-- 删除原表

DROP TABLE "DeviceData";

-- 将新表重命名

ALTER TABLE "DeviceData_temp" RENAME TO "DeviceData";