'Duplicate key' error when update column with list data type and table has a primary key about duckdb HOT 3 CLOSED

Hi @lpfy, thanks for raising this issue. As described in this (quite well-hidden) comment, updates on lists are implemented as as sequence of delete and insert operations, which can trigger duplicates. I'll update this in the documentation.

from duckdb.

@szarnyasg Thanks, I have noticed the new documentation is live. https://duckdb.org/docs/sql/data_types/list

from duckdb.

@szarnyasg can you update the documentation at https://duckdb.org/docs/sql/indexes#over-eager-unique-constraint-checking? This is the page that the error message points to (not the list page):

...If this is an unexpected constraint violation please double check with the known index limitations section in our documentation (https://duckdb.org/docs/sql/indexes)

Furthermore, the documentation on the index page implies that these limitations are on updates to the constrained columns themselves:

For example, in the following query, SET id = 1 causes a Constraint Error to occur

However, this error seems to greatly expand on the possible cause for such errors and it would be nice to know all types/cases where updates are turned into deletes and inserts, and to have this in 1 single place. Right now, we know that: (a) updating a column with a constraint and (b) updating a list. Are there more?

from duckdb.