Re: [sqlite] UPSERT clause does not work with "NOT NULL" constraint
Great, thanks! Best, Manuel On Thu, May 2, 2019 at 6:25 PM Richard Hipp wrote: > Documentation has been updated in an attempt to clarify when UPSERT > applies and when it does not. > > On 5/2/19, Manuel Rigger 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 wrote: > > > >> On 5/2/19, Manuel Rigger 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
Re: [sqlite] UPSERT clause does not work with "NOT NULL" constraint
Documentation has been updated in an attempt to clarify when UPSERT applies and when it does not. On 5/2/19, Manuel Rigger 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 wrote: > >> On 5/2/19, Manuel Rigger 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
Re: [sqlite] UPSERT clause does not work with "NOT NULL" constraint
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 wrote: > On 5/2/19, Manuel Rigger 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
Re: [sqlite] UPSERT clause does not work with "NOT NULL" constraint
On 5/2/19, Manuel Rigger 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
