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