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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
