- Define the duplicate key first: for periodic sensor readings it is usually (point, measurement timestamp), not a message ID and not the arrival time.
- Decide the winner rule: first copy wins, last copy wins, or highest sequence number or best quality wins. Exact copies make the rule irrelevant; corrected values make it essential.
- Find duplicates with
GROUP BY ... HAVING count(*) > 1, rank copies withrow_number() OVER (PARTITION BY key ORDER BY rule), and delete rank 2 and above. - Prevent them with a primary key on the dedupe key and
ON CONFLICT(PostgreSQL, SQLite) orON DUPLICATE KEY UPDATE/INSERT IGNORE(MySQL); collapse duplicates inside a batch first, or the upsert or MERGE fails. - Time-series engines differ: InfluxDB 3 merges points with the same table, tag set and timestamp; QuestDB deduplicates on declared UPSERT KEYS; ClickHouse ReplacingMergeTree removes duplicates only at merge time.
Scope: what a duplicate is in telemetry
This guide is for developers who load sensor and BMS readings into SQL tables and find the same reading twice in a trend chart or an energy total. Four kinds of duplicate need different handling:
- Exact redelivery. Same point, timestamp and value. MQTT QoS 1 is defined as "at least once", where duplicates can occur; a Kafka consumer that restarts before committing its offset re-reads messages; an operator re-runs a CSV import.
- Correction. Same point and timestamp, different value: a gateway re-sends after recalibration, or a meter reading is estimated and later replaced by the actual.
- Near duplicate. The same physical reading with timestamps a few milliseconds apart, or the same instant written in two text formats. A unique key does not catch these unless timestamps are normalised first.
- Two sources. Two gateways or a BMS and an IoT overlay both publish the same point. Decide which source is authoritative per point.
Making the consumer itself idempotent (offsets stored with the data, upserts, and the fencing safeguards needed before any exactly-once claim about database writes can be made) is covered in Kafka consumer idempotency for sensor streams; this page is about what the database does with the duplicates that still arrive.
Two decisions: the key and the winner
Use the measurement time from the device or gateway in the key, not the database insert time: two copies of one reading normally arrive at different times, so an arrival time in the key lets both in. Normalise timestamps before they reach the key (one time zone, one precision, one storage type), which the SQLite example below shows going wrong. Keep a seq or received column if the winner rule needs it.
| Data | Dedupe key | Winner rule | Why |
|---|---|---|---|
| Periodic readings (AHU temperature every minute) | (point_id, ts) | Highest gateway sequence number, else last received | One true value per point per instant; later copies may be corrections |
| Meter readings with estimates | (meter_id, ts) | Actual beats estimated (quality code), then latest | Billing and energy reports need the actual |
| Events (alarms, door access, work-order status changes) | Source event ID, or (asset, event type, ts) | First copy wins | Two identical alarms at one instant are one event |
| Counters (run hours, kWh register) | (point_id, ts) | Highest sequence; reject decreases unless a reset is flagged | Duplicates distort consumption calculated from differences |
Find and remove duplicates that already exist
A landing table without a key, as many first pipelines have, holds two duplicates for point 7: an exact QoS 1 redelivery at 08:01, and a corrected value with a later sequence number at 08:02. The queries count duplicate keys, rank the copies with row_number(), and delete everything except rank 1. The surrogate id column gives each physical row an identity for the delete.
SET TIME ZONE 'UTC';
CREATE TABLE landing (
id bigint GENERATED ALWAYS AS IDENTITY,
point_id integer NOT NULL,
ts timestamptz NOT NULL,
value double precision NOT NULL,
seq integer NOT NULL, -- gateway message sequence number
received timestamptz NOT NULL
);
INSERT INTO landing (point_id, ts, value, seq, received) VALUES
(7, '2026-10-27 08:00:00', 13.4, 101, '2026-10-27 08:00:01'),
(7, '2026-10-27 08:01:00', 13.6, 102, '2026-10-27 08:01:01'),
(7, '2026-10-27 08:01:00', 13.6, 102, '2026-10-27 08:01:09'), -- QoS 1 redelivery: exact copy
(7, '2026-10-27 08:02:00', 13.9, 103, '2026-10-27 08:02:01'),
(7, '2026-10-27 08:02:00', 14.1, 117, '2026-10-27 08:15:30'), -- same key, corrected value
(8, '2026-10-27 08:00:00', 612, 201, '2026-10-27 08:00:02');
-- 1. Find duplicate keys
SELECT point_id, ts, count(*) AS copies, count(DISTINCT value) AS distinct_values
FROM landing GROUP BY point_id, ts HAVING count(*) > 1 ORDER BY point_id, ts;
-- 2. Rank copies: keep the highest sequence number per key
SELECT id, point_id, ts, value, seq,
row_number() OVER (PARTITION BY point_id, ts ORDER BY seq DESC, received DESC) AS rn
FROM landing ORDER BY point_id, ts, rn;
-- 3. Delete everything except rank 1
DELETE FROM landing l
USING (SELECT id, row_number() OVER (PARTITION BY point_id, ts
ORDER BY seq DESC, received DESC) AS rn
FROM landing) d
WHERE l.id = d.id AND d.rn > 1;
SELECT point_id, ts, value, seq FROM landing ORDER BY point_id, ts; point_id | ts | copies | distinct_values
----------+------------------------+--------+-----------------
7 | 2026-10-27 08:01:00+00 | 2 | 1
7 | 2026-10-27 08:02:00+00 | 2 | 2
id | point_id | ts | value | seq | rn
----+----------+------------------------+-------+-----+----
1 | 7 | 2026-10-27 08:00:00+00 | 13.4 | 101 | 1
3 | 7 | 2026-10-27 08:01:00+00 | 13.6 | 102 | 1
2 | 7 | 2026-10-27 08:01:00+00 | 13.6 | 102 | 2
5 | 7 | 2026-10-27 08:02:00+00 | 14.1 | 117 | 1
4 | 7 | 2026-10-27 08:02:00+00 | 13.9 | 103 | 2
6 | 8 | 2026-10-27 08:00:00+00 | 612 | 201 | 1
point_id | ts | value | seq
----------+------------------------+-------+-----
7 | 2026-10-27 08:00:00+00 | 13.4 | 101
7 | 2026-10-27 08:01:00+00 | 13.6 | 102
7 | 2026-10-27 08:02:00+00 | 14.1 | 117
8 | 2026-10-27 08:00:00+00 | 612 | 201The same clean-up in MySQL
MySQL 8.0 supports the same window function; the delete uses the multi-table DELETE ... JOIN form. On the same four rows for point 7 at 08:01 and 08:02 it removed 2 rows and kept the 13.6 (seq 102) and 14.1 (seq 117) readings (run on MySQL 8.0.46):
DELETE l FROM landing l
JOIN (SELECT id, ROW_NUMBER() OVER (PARTITION BY point_id, ts ORDER BY seq DESC, id DESC) AS rn
FROM landing) d
ON d.id = l.id
WHERE d.rn > 1;
-- OK, affected rows: 2Prevent duplicates in PostgreSQL: key plus upsert
Give the target table a primary key on the dedupe key. A plain insert of a duplicate then fails with SQLSTATE 23505 (see PostgreSQL error 23505); ON CONFLICT DO NOTHING keeps the first copy silently; ON CONFLICT DO UPDATE ... WHERE implements "newer sequence wins" and ignores stale copies. PostgreSQL documents that ON CONFLICT DO UPDATE gives an atomic insert-or-update outcome even under high concurrency.
CREATE TABLE reading (
point_id integer NOT NULL,
ts timestamptz NOT NULL,
value double precision NOT NULL,
seq integer NOT NULL,
PRIMARY KEY (point_id, ts)
);
INSERT INTO reading VALUES (7, '2026-10-27 08:00:00', 13.4, 101);
INSERT INTO reading VALUES (7, '2026-10-27 08:00:00', 13.4, 101); -- plain duplicate
INSERT INTO reading VALUES (7, '2026-10-27 08:00:00', 13.4, 101)
ON CONFLICT (point_id, ts) DO NOTHING; -- ignored
-- Newer sequence number wins; an older copy is ignored
INSERT INTO reading VALUES (7, '2026-10-27 08:00:00', 13.5, 100)
ON CONFLICT (point_id, ts) DO UPDATE SET value = EXCLUDED.value, seq = EXCLUDED.seq
WHERE EXCLUDED.seq > reading.seq;
INSERT INTO reading VALUES (7, '2026-10-27 08:00:00', 13.7, 120)
ON CONFLICT (point_id, ts) DO UPDATE SET value = EXCLUDED.value, seq = EXCLUDED.seq
WHERE EXCLUDED.seq > reading.seq;
SELECT * FROM reading;-- plain duplicate
ERROR: duplicate key value violates unique constraint "reading_pkey"
DETAIL: Key (point_id, ts)=(7, 2026-10-27 08:00:00+00) already exists.
SQLSTATE 23505
point_id | ts | value | seq
----------+------------------------+-------+-----
7 | 2026-10-27 08:00:00+00 | 13.7 | 120Duplicates inside one batch
Ingestion services insert many rows per statement, and a replayed buffer can put the same key twice in one batch. ON CONFLICT DO UPDATE then fails with SQLSTATE 21000 (see PostgreSQL error 21000), because a statement may not update the same row twice. Collapse the batch with DISTINCT ON, ordered by the winner rule, before the upsert. MERGE has the same requirement: PostgreSQL asks that the join produce at most one source row per target row, and when two identical new rows were both unmatched, the run below inserted both and hit the primary key instead.
INSERT INTO reading VALUES (7, '2026-10-27 08:05:00', 14.0, 130),
(7, '2026-10-27 08:05:00', 14.0, 130)
ON CONFLICT (point_id, ts) DO UPDATE SET value = EXCLUDED.value, seq = EXCLUDED.seq;
-- ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
-- HINT: Ensure that no rows proposed for insertion within the same command
-- have duplicate constrained values.
-- SQLSTATE 21000
-- Fix: collapse the batch first, keeping the highest seq per key
INSERT INTO reading
SELECT DISTINCT ON (point_id, ts) point_id, ts, value, seq
FROM (VALUES (7, timestamptz '2026-10-27 08:05:00', 14.0, 130),
(7, timestamptz '2026-10-27 08:05:00', 14.2, 131)) AS b(point_id, ts, value, seq)
ORDER BY point_id, ts, seq DESC
ON CONFLICT (point_id, ts) DO UPDATE SET value = EXCLUDED.value, seq = EXCLUDED.seq
WHERE EXCLUDED.seq > reading.seq;
-- 08:05 is stored as 14.2, seq 131
-- MERGE from a staging table with an exact duplicate in it
CREATE TABLE staging (point_id integer, ts timestamptz, value double precision, seq integer);
INSERT INTO staging VALUES (7, '2026-10-27 08:07:00', 14.4, 133),
(7, '2026-10-27 08:07:00', 14.4, 133);
MERGE INTO reading r
USING staging s ON r.point_id = s.point_id AND r.ts = s.ts
WHEN MATCHED AND s.seq > r.seq THEN UPDATE SET value = s.value, seq = s.seq
WHEN NOT MATCHED THEN INSERT VALUES (s.point_id, s.ts, s.value, s.seq);
-- ERROR: duplicate key value violates unique constraint "reading_pkey"
-- DETAIL: Key (point_id, ts)=(7, 2026-10-27 08:07:00+00) already exists.
-- SQLSTATE 23505NULLs in the key
By default a unique constraint treats NULLs as distinct, so rows with a NULL in a key column never collide. For readings keyed on NOT NULL columns this does not arise; for event tables with optional columns (an alarm with no event code) it does. Since PostgreSQL 15, UNIQUE NULLS NOT DISTINCT changes this (the PostgreSQL 15 release notes list it). In the run, two identical rows with a NULL event_code were both accepted by a plain UNIQUE (asset_id, event_code, ts); with NULLS NOT DISTINCT the second raised 23505 with Key (asset_id, event_code, ts)=(3, null, 2026-10-27 08:00:00+00) already exists.
MySQL and SQLite equivalents
MySQL. A duplicate primary key raises error 1062 (see MySQL error 1062). INSERT IGNORE discards the row and turns the error into a warning; INSERT ... ON DUPLICATE KEY UPDATE updates the existing row. Since MySQL 8.0.19 a row alias (AS new) refers to the incoming values; VALUES(col) in this clause is deprecated from 8.0.20. MySQL has no WHERE on the update, so the winner rule goes into IF() expressions. Put seq last. The ON DUPLICATE KEY UPDATE page we checked does not state the order in which its assignments are evaluated; the UPDATE page says single-table assignments are generally evaluated from left to right, and a run on MySQL 8.0.46 behaved that way here: with the seq assignment first, the seq 120 copy updated seq to 120 but left value at 13.4, because the value test then compared against the new seq. Because the documented wording is "generally", check this on your version and keep the comparison column last. Note that IGNORE also suppresses other ignorable errors, not only duplicates.
CREATE TABLE reading (
point_id INT NOT NULL,
ts DATETIME(3) NOT NULL,
value DOUBLE NOT NULL,
seq INT NOT NULL,
PRIMARY KEY (point_id, ts)
);
INSERT INTO reading VALUES (7, '2026-10-27 08:00:00', 13.4, 101);
INSERT INTO reading VALUES (7, '2026-10-27 08:00:00', 13.4, 101);
INSERT IGNORE INTO reading VALUES (7, '2026-10-27 08:00:00', 13.4, 101);
INSERT INTO reading VALUES (7, '2026-10-27 08:00:00', 13.7, 120) AS new
ON DUPLICATE KEY UPDATE
value = IF(new.seq > reading.seq, new.value, reading.value),
seq = IF(new.seq > reading.seq, new.seq, reading.seq);
INSERT INTO reading VALUES (7, '2026-10-27 08:00:00', 13.5, 100) AS new
ON DUPLICATE KEY UPDATE
value = IF(new.seq > reading.seq, new.value, reading.value),
seq = IF(new.seq > reading.seq, new.seq, reading.seq);
SELECT * FROM reading;-- second plain INSERT
ERROR 1062 (23000): Duplicate entry '7-2026-10-27 08:00:00.000' for key 'reading.PRIMARY'
-- INSERT IGNORE: affected rows 0
Warning (Code 1062): Duplicate entry '7-2026-10-27 08:00:00.000' for key 'reading.PRIMARY'
-- seq 120 upsert: affected rows 2 (row updated); seq 100 upsert: affected rows 0 (unchanged)
-- (separate run with the seq assignment written first: value stayed 13.4, seq became 120)
+----------+-------------------------+-------+-----+
| point_id | ts | value | seq |
+----------+-------------------------+-------+-----+
| 7 | 2026-10-27 08:00:00.000 | 13.7 | 120 |
+----------+-------------------------+-------+-----+SQLite, and the timestamp format trap
SQLite follows PostgreSQL's ON CONFLICT syntax, including DO UPDATE ... WHERE. A duplicate key without it fails with UNIQUE constraint failed (see the SQLite error page). SQLite has no native timestamp type, so timestamps stored as text compare as text: the same instant written as 08:00:00Z and 08:00:00.000Z are two different keys, and the run below stored both. Normalise the format in the ingestion service, or store Unix epoch integers.
CREATE TABLE reading (
point_id INTEGER NOT NULL,
ts TEXT NOT NULL,
value REAL NOT NULL,
seq INTEGER NOT NULL,
PRIMARY KEY (point_id, ts)
);
INSERT INTO reading VALUES (7, '2026-10-27T08:00:00Z', 13.4, 101);
INSERT INTO reading VALUES (7, '2026-10-27T08:00:00Z', 13.4, 101);
-- ERROR: UNIQUE constraint failed: reading.point_id, reading.ts
INSERT INTO reading VALUES (7, '2026-10-27T08:00:00Z', 13.7, 120)
ON CONFLICT (point_id, ts) DO UPDATE SET value = excluded.value, seq = excluded.seq
WHERE excluded.seq > reading.seq;
INSERT INTO reading VALUES (7, '2026-10-27T08:00:00.000Z', 99.9, 121); -- accepted
SELECT * FROM reading ORDER BY ts;
-- 7 | 2026-10-27T08:00:00.000Z | 99.9 | 121
-- 7 | 2026-10-27T08:00:00Z | 13.7 | 120How time-series engines treat duplicates
Two practical consequences. In InfluxDB, a per-message ID written as a tag makes every point unique, so redeliveries are stored as separate points; keep such IDs out of the tag set (see high-cardinality tags). In ClickHouse, a ReplacingMergeTree table is a de-duplication target that converges over time, not a constraint; reports that must be exact should query with FINAL or aggregate by the key.
-- QuestDB: last write wins per (ts, point)
CREATE TABLE reading (
ts TIMESTAMP, point SYMBOL, value DOUBLE
) TIMESTAMP(ts) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(ts, point);
-- ClickHouse: rows with the same (point_id, ts) collapse at merge time;
-- the version column keeps the row with the highest seq
CREATE TABLE reading (
point_id UInt32, ts DateTime64(3), value Float64, seq UInt64
) ENGINE = ReplacingMergeTree(seq)
ORDER BY (point_id, ts);
SELECT * FROM reading FINAL WHERE point_id = 7;| Engine | What counts as the same point | What happens | Watch out for |
|---|---|---|---|
| InfluxDB 3 Core | Same table, tag set and timestamp | One point is stored; field sets of the writes are merged | When duplicate writes set the same field, the documentation says it does not guarantee which value is kept |
| QuestDB | Columns declared in DEDUP UPSERT KEYS (must include the designated timestamp) | The new row replaces the old one (last write wins) | Requires WAL tables; enabling it does not deduplicate existing data |
| ClickHouse ReplacingMergeTree | Same sorting key (ORDER BY) | Duplicates removed during background merges; with a version column, the row with the highest version is kept | Merges happen at an unknown time; queries may need FINAL or aggregation |
| TimescaleDB | Your primary key or unique index, which must include the partitioning column | Standard PostgreSQL behaviour, including ON CONFLICT | Inserts into columnstore chunks must check the constraint, which costs extra work |
Trade-offs, limitations and when not to deduplicate
- Unique keys cost write work. Every insert checks an index, and TimescaleDB documents extra work for inserts into columnstore chunks with a unique constraint. For append-only data that cannot contain duplicates, Tiger Data suggests considering whether the constraint is needed at all.
- Dedupe at read time instead. If the load path must stay constraint-free, keep a view that applies
DISTINCT ONorrow_number()and run periodic clean-ups. Queries pay the cost instead. - The key can be wrong. Two genuinely different readings at the same millisecond (a fast change-of-value sensor) are merged by a (point, ts) key. If that can happen, add a sequence to the key.
- Do not deduplicate raw evidence. If you must prove what a device sent (warranty, billing disputes), keep the raw landing data, duplicates included, for a period, and deduplicate into the reporting table.
- Roll-ups. Duplicates that reached a roll-up inflate sums and counts; after a clean-up, re-run the roll-up for the affected buckets (see continuous aggregates vs materialized views).
Operations and monitoring
- Measure the duplicate rate. Count conflicts (rows affected versus rows sent) per batch and per gateway; a sudden rise usually means a gateway replaying its buffer or a consumer stuck in a restart loop.
- Log corrections separately. When a later copy changes a value, write the old and new values to an audit table; maintenance engineers and energy managers will ask why a figure changed.
- Security. The ingestion role needs
INSERTandUPDATEon the readings table for upserts, but notDELETE; run clean-up jobs under a separate role. - Test with replays. Before go-live, replay a day of captured messages twice and check that row counts and daily totals do not change. Try the PostgreSQL examples in the PostgreSQL playground.
Frequently asked questions
How do I find duplicate rows in time-series data?
Group by the dedupe key and keep groups with more than one row: SELECT point_id, ts, count(*) FROM reading GROUP BY point_id, ts HAVING count(*) > 1. Add count(DISTINCT value) to separate exact copies from corrections.
How do I delete duplicates but keep one row?
Number the copies with row_number() OVER (PARTITION BY point_id, ts ORDER BY seq DESC) and delete rows whose number is greater than 1, joining on a unique row identifier.
Should I use ON CONFLICT DO NOTHING or DO UPDATE?
DO NOTHING when copies are exact or the first copy should win; DO UPDATE ... WHERE EXCLUDED.seq > table.seq when later copies can carry corrections.
Why does my upsert fail with "cannot affect row a second time"?
The batch itself contains the same key twice. Collapse it with DISTINCT ON (or GROUP BY) ordered by your winner rule before the INSERT ... ON CONFLICT.
Does InfluxDB store duplicate points?
InfluxDB 3 identifies a point by table, tag set and timestamp and stores a single point for that identity, merging field sets; when duplicate writes set the same field, its documentation does not guarantee which value is kept.
Sources
- PostgreSQL 18 documentation: INSERT
- PostgreSQL 18 documentation: MERGE
- PostgreSQL 18 documentation: Constraints
- PostgreSQL 18 documentation: SELECT
- PostgreSQL 18 documentation: Window Functions
- MySQL 8.0 Reference Manual: INSERT ... ON DUPLICATE KEY UPDATE
- MySQL 8.0 Reference Manual: INSERT
- MySQL 8.0 Reference Manual: UPDATE
- SQLite: UPSERT
- OASIS MQTT Version 5.0 specification
- InfluxDB 3 Core docs: Line protocol reference
- QuestDB docs: Deduplication
- ClickHouse docs: ReplacingMergeTree
- Tiger Data docs: Primary keys, time columns, and uniqueness
- PostgreSQL 15.0 release notes
- QuestDB docs: CREATE TABLE
Checked 27 October 2026.
How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.