Re: Duplicate key violation on upsert

On 21/03/2020, at 8:10 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

The _hyper_1_1931_chunk_da_datum_x_acc_idx index has the same definition as the da_datum_x_acc_idx above (it is defined on a child table). That is, they are both essentially:
UNIQUE, btree (node_id, source_id, ts DESC, jdata_a) WHERE jdata_a IS NOT NULL
The da_datum_pkey index is what the ON CONFLICT cause refers to, so (node_id, ts, source_id) is UNIQUE as well.

Hmm, wonder if you are getting bit by this?:

https://www.postgresql.org/docs/12/sql-insert.html#SQL-ON-CONFLICT

"INSERT with an ON CONFLICT DO UPDATE clause is a “deterministic” statement. This means that the command will not be allowed to affect any single existing row more than once; a cardinality violation error will be raised when this situation arises. Rows proposed for insertion should not duplicate each other in terms of attributes constrained by an arbiter index or constraint.”