Documentation has been updated in an attempt to clarify when UPSERT applies and when it does not.
On 5/2/19, Manuel Rigger <rigger.man...@gmail.com> wrote: > Okay, thanks for the clarification! > > I think that this part of the documentation is ambiguous. The part of the > documentation that you quoted mentions a "conflict target", but there is no > conflict target in the example that I provided. The documentation > continues by stating that a conflict target is not necessary and that "A DO > NOTHING upsert without a conflict target works the same as an INSERT OR > IGNORE." Would it maybe be helpful to update the documentation to > explicitly state that UPSERT does not apply to NOT NULL constraints, and > that apart from this case DO NOTHING works in the same way as INSERT OR > IGNORE? > > Best, > Manuel > > On Thu, May 2, 2019 at 5:38 PM Richard Hipp <d...@sqlite.org> wrote: > >> On 5/2/19, Manuel Rigger <rigger.man...@gmail.com> wrote: >> > Hi everyone, >> > >> > It seems that upsert does not take into account "NOT NULL" constraints. >> In >> > the example below, I get an error "NOT NULL constraint failed: >> > test.c0": >> > >> > CREATE TABLE test (c0 NOT NULL); >> > INSERT INTO test(c0) VALUES (NULL) ON CONFLICT DO NOTHING; >> > >> > I would have expected that the second statement has the same effect as >> the >> > following statement, which would also be confirmed by the docs [1]: >> > >> > INSERT OR IGNORE INTO test(c0) VALUES (NULL); >> > >> > The example seems to work for the UNIQUE and PRIMARY KEY constraints. >> >> UPSERT is not standard SQL - it is a PostgreSQL extension that we have >> attempted to replicate. PostgreSQL behaves the same way in this test >> (a fact that I have just now verified on sqlfiddle.com). The ON >> CONFLICT clause is only triggered by uniqueness constraints, not NOT >> NULL constraints. >> >> The UPSERT documentation says "The conflict target specifies a >> specific uniqueness constraint that will trigger the upsert." So it >> does not explicitly say that UPSERT does not work for NOT NULL >> constraints, but that is the implication. >> >> >> -- >> D. Richard Hipp >> d...@sqlite.org >> > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
