Postgresql删除数据库表中重复数据的几种方法详解（post startup error）深度揭秘

文章摘要

这篇文章讨论了如何在PostgreSQL数据库中优化处理大数据量（70万条记录）的表，并解决重复数据的问题。以下是总结：文章描述了一张名为`devicedata`的表，主键为`Id`（程序生成的GUID），查询效率较低。作者考虑将`DeviceId`和`Timestamp`作为主键，以提高查询效率，但由于数据量大，直接切换主键不可行，需要先去重。作者提出使用以下方法去重： 1. 创建新表`DeviceData_temp`，并使用`SELECT DISTINCT`提取`DeviceId`和`Timestamp`，将数据复制到新表中。 2. 使用`DROP TABLE`删除原表`DeviceData`，然后通过`ALTER TABLE`将新表重命名为`DeviceData`。然而，这种方法存在以下问题： - 删除原表`DeviceData`后，新表`DeviceData_temp`可能无法保留`DataArray`字段。 - `SELECT DISTINCT`可能导致部分数据丢失。为了解决这些问题，作者建议使用`INSERT`语句，并结合`ON CONFLICT`和外键约束： 1. 创建新表`DeviceData_temp`，设置主键为`DeviceId`和`Timestamp`。 2. 在旧表`DeviceData`上创建外键约束，引用新表的主键。 3. 使用`INSERT`语句，结合`ON CONFLICT DO NOTHING`，将数据插入到新表中，自动处理重复数据。 4. 删除旧表`DeviceData`，并将新表重命名为`DeviceData`。这种方法可以高效去重，避免直接切换主键带来的性能问题。此外，作者还提到可以使用联合索引加速查询，进一步提升性能。总结来说，文章提供了一种高效处理大数据量重复数据的方法，适用于类似场景。

一直使用Postgresql数据库，有一张表是这样的：

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”);

主键为Id，是通过程序生成的GUID，随着数据表的越来越大（70w），即便我建立了索引，查询效率依然不乐观。

使用GUID作为数据库的主键对分布式应用比较友好，但是不利于数据的插入，可以使用类似ABP的方法生成连续的GUID解决这个问题。

为了进行优化，计划使用DeviceId与Timestamp作为主键，由于主键会自动建立索引，使用这两个字段查询的时候，查询效率可以有很大的提升。不过，由于数据库的插入了很多的重复数据，直接切换主键不可行，需要先剔除重复数据。

数据量小的时候适用。对于我这个70w的数据，查询运行了半个多小时也无法完成。

DELETE FROM “DeviceData”
WHERE “Id”
NOT IN (
SELECT max(“Id”)
FROM “DeviceData_temp”
GROUP BY “DeviceId”, “Timestamp”
);

建立一张新表然后插入数据，或者使用select into语句。

SELECT DISTINCT “Timestamp”, “DeviceId”
INTO “DeviceData_temp”
FROM “DeviceData”;
— 删除原表
DROP TABLE “DeviceData”;
— 将新表重命名
ALTER TABLE “DeviceData_temp” RENAME TO “DeviceData”;

不过这个问题也非常大，很明显，未来的表，是不需要Id列的，但是DataArray也没有了，没有意义。

如果SELECT DISTINCT “Timestamp”, “DeviceId”, “DataArray”，那么可能出现”Timestamp”, “DeviceId”重复的现象。

如果我们直接建立新表格，设置好新的主键，然后插入数据，如果重复了就跳过不就行了？但是使用select into是不行了，重复的数据会导致语句执行中断。需要借助upsert（on conflict）方法。

INSERT INTO “DeviceData_temp”
SELECT * FROM “DeviceData”
on conflict(“DeviceId”, “Timestamp”) DO NOTHING;
— 删除原表
DROP TABLE “DeviceData”;
— 将新表重命名
ALTER TABLE “DeviceData_temp” RENAME TO “DeviceData”;

执行不到100s就完成了，删除了许多重复数据。

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对脚本之家的支持。如果你想了解更多相关内容请查看下面相关链接

您可能感兴趣的文章:postgresql 删除重复数据的几种方法小结postgresql 删除重复数据案例详解postgresql如何找到表中重复数据的行并删除