Great, thanks! Best, Manuel
On Thu, May 2, 2019 at 6:25 PM Richard Hipp <d...@sqlite.org> wrote: > 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
